1

I am trying to get the row number where my partNum is located at. The partNum is located at row 42. However, when I run my code, i get 41 instead. I was following an example I seen here but then I ran into this issue. I am not sure if maybe I am missing something?

Function FindRFIDCode(ByVal partNum As String) As Integer

    FindRFIDCode = 0
    Dim matchResult As Variant
    Dim tbl As ListObject
    Set tbl = Range("CombinedTapeInfo").ListObject
    
    matchResult = Application.Match(partNum, tbl.ListColumns(1).DataBodyRange, 0)
    
    If IsError(matchResult) Then
        FindRFIDCode = 0
    Else
        FindRFIDCode = matchResult
    End If
    
End Function
Ike
  • 9,580
  • 4
  • 13
  • 29
Tony
  • 11
  • 2
  • 1
    How about `FindRFIDCode = matchResult + tbl.HeaderRowRange.Row`? – SJR Aug 04 '23 at 13:57
  • `DataBodyRange` excludes header row - therefore within `databodyrange` it is the 41st row - not 42nd (which is presumably the row number of the sheet) – Ike Aug 04 '23 at 13:59
  • @SJR I tried it and my FindRFIDCode = 42 now, thank you – Tony Aug 04 '23 at 14:03
  • @Ike I did not know that, thank you for the information! – Tony Aug 04 '23 at 14:04
  • 2
    SIde note: [change `Integer` to `Long`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Aug 04 '23 at 14:04
  • 1
    Another side note: Match will find the row number within the table not within the sheet - so if your table header is on row 3 and your part number is on the next row it will return 1 and not 4. – Darren Bartrup-Cook Aug 04 '23 at 14:21

1 Answers1

2

I'm new here and tried to guide you in the right direction. If you are referencing to a table, then you should use the table name or index. The result 41 is the index number of the table row, the databodyrange, without the table header. Make sure that declared variables are in line with the data (string, integer). I wrote some additional code to print the result in D2 and used C2 as input range for the number to be found.

Sub Test()
Dim partNum As String
    partNum = ThisWorkbook.Worksheets(1).Range("C2")
Dim i As Integer
    i = FindRFIDCode(partNum)
    ThisWorkbook.Worksheets(1).Range("D2") = i
End Sub

Function FindRFIDCode(ByVal partNum As String) As Integer

FindRFIDCode = 0
Dim matchResult As Variant
Dim iRow As Integer
Dim tbl As ListObject
'Set tbl = Range("CombinedTapeInfo").ListObject
Set tbl = ThisWorkbook.Worksheets(1).ListObjects("tbl_Fruits")
matchResult = Application.Match(partNum, tbl.ListColumns(1).DataBodyRange, 0)
iRow = tbl.ListColumns(1).DataBodyRange(matchResult).Row

If IsError(matchResult) Then
    FindRFIDCode = 0
Else
    FindRFIDCode = iRow
End If

End Function
Bas N
  • 21
  • 2