Matching data from two worksheets with primary key in vba (error 91 occurs)

Question

I try to match the data in two worksheets. I have a primary key and one column of assigned data in each of the worksheets. The goal is to match the assigned data without showing the primary key in a third worksheet.I'm also open for other solution ideas. Thank you very much!

In the debug mode the line:

lgfindRow = ThisWorkbook.Sheets("Labeltexte").Columns(1).Find(strPIN, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True).row

is highlighted.

The complete sub:

Sub MatchData()

    Dim datasheet As Worksheet
    Dim lgCount As Long
    Dim intlastRow As Integer
    Dim strPIN As String
    Dim intcolumn As Integer
    Dim intcheck As Integer
    Dim intrange As Integer
    Dim lgfindRow As Long
    Dim intcancel As Integer
    
    Set datasheet = ThisWorkbook.Worksheets("Produktdaten")
    intlastRow = ThisWorkbook.Sheets("Artikelnummern").UsedRange.SpecialCells(xlCellTypeLastCell).row
    
    For lgCount = 1 To intlastRow
        intcolumn = 3
        intcheck = 1
        intcancel = 0
        strPIN = ThisWorkbook.Sheets("Artikelnummern").Cells(lgCount, 1).Value
        datasheet.Cells(lgCount, 1).Value = strPIN
        datasheet.Cells(lgCount, 2).Value = ThisWorkbook.Sheets("Artikelnummern").Cells(lgCount, 2).Value
        lgfindRow = ThisWorkbook.Sheets("Labeltexte").Columns(1).Find(strPIN, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True).row**
        Do Until ThisWorkbook.Sheets("Labeltexte").Cells(lgfindRow + intcheck, 1).Value <> "" Or intcancel = 10
            intcheck = intcheck + 1
            intcancel = intcancel + 1
        Loop
        For intrange = 0 To intcheck - 1
            datasheet.Cells(lgCount, intcolumn).Value = ThisWorkbook.Sheets("Labeltexte").Cells(lgfindRow + intrange, 2).Value
            intcolumn = intcolumn + 1
        Next
    Next
    
End Sub

Does this post help you? https://stackoverflow.com/questions/18927297/vba-run-time-error-91#:~:text=Runtime%20error%2091%20%22object%20variable,doesn't%20use%20that%20keyword.&text=SourceWindow%20is%20an%20object%2C%20assigned,call%20fails%20with%20error%2091. — Dominique, Jul 19 '20 at 09:31
What's the value of `strPin` when it errors & is that value in the column so it can be found? `Find` returns a range object which you get the row property from. If the range isn't found then it can't get the row so an error is thrown. Can't remember off hand what error 91 is... something to do with objects I think. — Darren Bartrup-Cook, Jul 19 '20 at 09:41
Before getting the row property, check that the object is not nothing. Or use Power Query. — Ron Rosenfeld, Jul 19 '20 at 10:03
That line ends with `.row**`. Is that a typo? If you just want to hightlight the line, you should use comments e.g. `.row '** error line`. As @RonRosenfeld suggested `Set` the result of `Find` to a `Range` variable and check if it `Is Nothing` before trying to access its properties. Also, you're starting the outer `For` loop from `1`. If you have header rows, shouldn't you start from `2`? — Super Symmetry, Jul 19 '20 at 12:17
It would help your question a lot to show a screenshot or example of both your data sheets, instead of having us guess from your code what the second sheet looks like... — Tim Williams, Jul 19 '20 at 16:55

Matching data from two worksheets with primary key in vba (error 91 occurs)

0 Answers0