By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Run-Time error '1004'. Application- defined or object-defined error

P: 1
Hi there

I am new to this forum so would appreciate any help that is on offer.

I am writing some VB in Excel and getting the "Run-Time error '1004'. Application- defined or object-defined error".

I have two spread sheets where I am trying to merge some data together. One spread sheet (Ad_Hoc_Test.xls) has Policy details on and the other (PA Claims Test.xls) has claims data on.

My code is basically supposed to loop through the source sheet (PA Claims Test.xls), find any matching policyID's that are also in the destination sheet (Ad_Hoc_Test.xls), then paste the row of relevant claim data next to the PolicyID data in the destination sheet.

My code loops through the source sheet fine but the first time around but then when it reaches the end of the sheet it does not go to the next Policy ID and that’s when I get the error on line -

Expand|Select|Wrap|Line Numbers
  1.  
  2. Do While arrDetails(2, 0) <> xlSheetDestination.Cells(y1, 5)
Here is the full code -

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub ImportClaims()
  3.  
  4.     Dim xlSheetSource As Worksheet
  5.     Dim xlSheetDestination As Worksheet
  6.     Dim y As Long
  7.     Dim y1 As Long
  8.     Dim x As Long
  9.     Dim policyID As String
  10.     Dim arrDetails()
  11.     Dim i As Long
  12.     Dim xlBookDestination As Workbook
  13.     Dim xlBookSource As Workbook
  14.  
  15.     Set xlBookDestination = Workbooks.Open("\\...(removed_for_question\Analysis\PA\Ad_Hoc_Test.xls")
  16.     Set xlSheetDestination = xlBookDestination.Sheets("Web")
  17.     Set xlBookSource = Workbooks.Open("\\...(removed_for_question\Analysis\PA\PA Claims Test.xls")
  18.     Set xlSheetSource = xlBookSource.Sheets("Data_Totals")
  19.  
  20.     'Row variable for xlSheetSource
  21.     y = 2
  22.  
  23.     'row variable for xlSheetDestination
  24.     y1 = 1
  25.  
  26.     'Start to loop though source file
  27.     Do While Len(xlSheetSource.Cells(y, 1)) <> 0
  28.  
  29.         policyID = xlSheetSource.Cells(y, 3)
  30.  
  31.         ReDim arrDetails(5, 0)
  32.  
  33.         x = 0
  34.         'Start looping and continue whilst on the same policy
  35.         Do While xlSheetSource.Cells(y, 3) = policyID
  36.  
  37.             ReDim Preserve arrDetails(5, x)
  38.             arrDetails(0, x) = xlSheetSource.Cells(y, 1)
  39.             arrDetails(1, x) = xlSheetSource.Cells(y, 2)
  40.             arrDetails(2, x) = xlSheetSource.Cells(y, 3)
  41.             arrDetails(3, x) = xlSheetSource.Cells(y, 4)
  42.             arrDetails(4, x) = xlSheetSource.Cells(y, 5)
  43.             arrDetails(5, x) = xlSheetSource.Cells(y, 6)
  44.  
  45.             y = y + 1
  46.             x = x + 1
  47.  
  48.         Loop
  49.  
  50.         'Loop though array to find matching policies
  51.  
  52.         Do While arrDetails(2, 0) <> xlSheetDestination.Cells(y1, 5)
  53.  
  54.         y1 = y1 + 1
  55.  
  56.         Loop
  57.  
  58.         If xlSheetDestination.Cells(y1, 5) = arrDetails(2, i) Then
  59.  
  60.                 'Insert Claim Id to destination
  61.                 xlSheetDestination.Cells(y1, 10) = arrDetails(0, 0)
  62.                 'Insert Insured to destination
  63.                 xlSheetDestination.Cells(y1, 11) = arrDetails(1, 0)
  64.                 'Insert Policy to destination
  65.                 xlSheetDestination.Cells(y1, 12) = arrDetails(2, 0)
  66.                 'Insert Assured Id to destination
  67.                 xlSheetDestination.Cells(y1, 13) = arrDetails(3, 0)
  68.                 'Insert Detail to destination
  69.                 xlSheetDestination.Cells(y1, 14) = arrDetails(4, 0)
  70.                 'Insert Total Payments to destination
  71.                 xlSheetDestination.Cells(y1, 15) = arrDetails(5, 0)
  72.  
  73.         End If
  74.  
  75.         y = y + 1
  76.  
  77.     Loop
  78.  
  79.     xlSheetDestination.Parent.Save
  80.     xlSheetSource.Parent.Close
  81.  
  82. End Sub
  83.  
Any help would be greatly appreciated.

Thanks

Matt
Oct 18 '11 #1
Share this Question
Share on Google+
1 Reply


Guido Geurs
Expert 100+
P: 767
The var "i" has no value !
If it's still not working, is it possible to attach a part of the XLS files (with some data )( fictive data if necessarily) so we can see what the structure is ?
Oct 19 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.