VBA table row relative reference

[John BX]

My code below works ok, as long as the table is at row 1 on the sheet. I would like it to work with the table placed anywhere not just row 1.
the problem seems to be with rngFound.row which returns the row number on the sheet, not the table.

Can somebody help?
Thanks

CompanyName	Grade	Rate1	Rate2
Company_A	PayGrade01	10	20
Company_A	PayGrade02	15	25
Company_Z	PayGrade01	20	25
Company_Z	PayGrade02	25	30

<tbody>
</tbody>

Sub getRate()


    Dim rngFound As Range
    Dim strFirst As String
    Dim strCompany As String
    Dim strGrade As String


    strCompany = "Company_Z"
    strGrade = "PayGrade02"


    Set rngFound = Range("tblRates[CompanyName]").Find(strCompany, , xlValues, xlWhole)
    If Not rngFound Is Nothing Then
        strFirst = rngFound.Address
        Do
            Debug.Print Range("tblRates[Grade]")(rngFound.Row - 1).Value
            Debug.Print rngFound.Row - 1
            If Range("tblRates[Grade]")(rngFound.Row - 1) = strGrade Then
            
                'Found
                MsgBox "Found a match at row: " & rngFound.Row & Chr(10) & _
                       "Rate 1: " & Range("tblRates[Rate1]")(rngFound.Row - 1) & Chr(10) & _
                       "Rate 2: " & Range("tblRates[Rate2]")(rngFound.Row - 1)
            End If
            Set rngFound = Range("tblRates[CompanyName]").Find(strCompany, rngFound, xlValues, xlWhole)
        Loop While rngFound.Address <> strFirst
    Else
        MsgBox "Rate not found"
    End If


    Set rngFound = Nothing


End Sub

 

[RoryA]

Subtract Range("tblRates").Row rather than 1.

 

[John BX]

Thanks for replying.
I replaced all the 1s with Range("tblRates").row but it now returns incorrect values for Rate1 and Rate2. Is there something else I need to change?

 

[RoryA]

Sorry - forgot about the header row being skipped in a simple reference. You need:

Range("tblRates[#All]").Row

 

[John BX]

Thanks. It works now.