Livin404
Well-known Member
- Joined
- Jan 7, 2019
- Messages
- 743
- Office Version
- 365
- 2019
- Platform
- Windows
Greetings, I am having a little difficulty with a VLOOKup through VBA. My current VBA works great except with I have a number in Column G specifically I get a totally different listing than what it should be. In column G I have a situation where I would have 3 or 7 characters. When I just had the 3 characters the VBA was perfect including when I had 777. Due to a new requirement some of the characters in Column G had to be increased to 7, so I had the change the formula in the macro to read the last three in Column G. Since then, I am not getting the correct data only when 777 is displayed. In the imagine you can see a portion of Column G. I tried left aligning Column G deleting the 777 and retyping it and I am still getting the incorrect response. Remember when the whole Column had only three characters and I searched for just three it was perfect. Below is my Macro, I am hoping just a little tweak to sort out getting an error with the number 777. Please note the Application.WorksheetFunction. I know that is correct. Thank you
VBA Code:
Private Sub VLOOK_UP()
Dim i As Integer
For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
Worksheets("72 Hr").Cells(i, 11).Value = _
Application.WorksheetFunction.VLookup(Right(Worksheets("72 Hr").Cells(i, 7).Value, 3), _
Worksheets("3 LTR").Range("A:B"), 2, 1)
Next i
End Sub