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

[mattmasters]

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 -

 
Do While arrDetails(2, 0) <> xlSheetDestination.Cells(y1, 5)

Here is the full code -

 
Private Sub ImportClaims()
 
    Dim xlSheetSource As Worksheet
    Dim xlSheetDestination As Worksheet
    Dim y As Long
    Dim y1 As Long
    Dim x As Long
    Dim policyID As String
    Dim arrDetails()
    Dim i As Long
    Dim xlBookDestination As Workbook
    Dim xlBookSource As Workbook
 
    Set xlBookDestination = Workbooks.Open("\\...(removed_for_question\Analysis\PA\Ad_Hoc_Test.xls")
    Set xlSheetDestination = xlBookDestination.Sheets("Web")
    Set xlBookSource = Workbooks.Open("\\...(removed_for_question\Analysis\PA\PA Claims Test.xls")
    Set xlSheetSource = xlBookSource.Sheets("Data_Totals")
 
    'Row variable for xlSheetSource
    y = 2
 
    'row variable for xlSheetDestination
    y1 = 1
 
    'Start to loop though source file
    Do While Len(xlSheetSource.Cells(y, 1)) <> 0
 
        policyID = xlSheetSource.Cells(y, 3)
 
        ReDim arrDetails(5, 0)
 
        x = 0
        'Start looping and continue whilst on the same policy
        Do While xlSheetSource.Cells(y, 3) = policyID
 
            ReDim Preserve arrDetails(5, x)
            arrDetails(0, x) = xlSheetSource.Cells(y, 1)
            arrDetails(1, x) = xlSheetSource.Cells(y, 2)
            arrDetails(2, x) = xlSheetSource.Cells(y, 3)
            arrDetails(3, x) = xlSheetSource.Cells(y, 4)
            arrDetails(4, x) = xlSheetSource.Cells(y, 5)
            arrDetails(5, x) = xlSheetSource.Cells(y, 6)
 
            y = y + 1
            x = x + 1
 
        Loop
 
        'Loop though array to find matching policies
 
        Do While arrDetails(2, 0) <> xlSheetDestination.Cells(y1, 5)
 
        y1 = y1 + 1
 
        Loop
 
        If xlSheetDestination.Cells(y1, 5) = arrDetails(2, i) Then
 
                'Insert Claim Id to destination
                xlSheetDestination.Cells(y1, 10) = arrDetails(0, 0)
                'Insert Insured to destination
                xlSheetDestination.Cells(y1, 11) = arrDetails(1, 0)
                'Insert Policy to destination
                xlSheetDestination.Cells(y1, 12) = arrDetails(2, 0)
                'Insert Assured Id to destination
                xlSheetDestination.Cells(y1, 13) = arrDetails(3, 0)
                'Insert Detail to destination
                xlSheetDestination.Cells(y1, 14) = arrDetails(4, 0)
                'Insert Total Payments to destination
                xlSheetDestination.Cells(y1, 15) = arrDetails(5, 0)
 
        End If
 
        y = y + 1
 
    Loop
 
    xlSheetDestination.Parent.Save
    xlSheetSource.Parent.Close
 
End Sub
 

Any help would be greatly appreciated.

Thanks

Matt

[Guido Geurs]

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 ?