VLOOKUP and right three letters

[Livin404]

Greetings, I have a VBA that works great where if the three letter code "ACV" is provided it will search over 10K rows for the appropriate response. However, on occasion I may be given a 7 digit character "BGR-ACV". I am hoping someone can let me know how I can insert a rule where it will search only the last three characters to the right. My current VBA is:

Private Sub VLOOK_UP()
    Dim i As Integer
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        Worksheets("72 Hr").Cells(i, 12).Value = _
        Application.WorksheetFunction.VLookup(Worksheets("72 Hr").Cells(i, 8).Value, _
        Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub

I think need something like

VLOOKUP(RIGHT(A2,3)

but I don't know how or where to slip something like that in. Thank you,

 

[Joe4]

Is that what you are looking for?

Private Sub VLOOK_UP()
    Dim i As Integer
    Dim lkup as String
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        lkup = Right(Worksheets("72 Hr").Cells(i, 8).Value, 3)
        Worksheets("72 Hr").Cells(i, 12).Value = _
            Application.WorksheetFunction.VLookup(lkup, _
            Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub

 

[Livin404]

HI, I just sorted it out about 20 minutes ago and it works great. Here is the Code and I think it is just like yours, or very similar. I was about to post this on here, to let people know. I'll give you the check mark for I'm sure it is spot on. You have always been very helpful. Thank you,

Private Sub VLOOK_UP()
    Dim i As Integer
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        Worksheets("72 Hr").Cells(i, 12).Value = _
        Application.WorksheetFunction.VLookup(Right(Worksheets("72 Hr").Cells(i, 8).Value, 3), _
Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub

 

[Joe4]

You are welcome.
Yes, the only real difference between our code is that I did the function on the lookup variable separately, so as not to overcomplicate the formula.
Either way works fine, neither one is more correct than the other.