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
<tbody>
</tbody>
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>
Code:
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
Last edited: