Getting a incorrect value in a VLOOKUP to a otherwise perfect Macro.

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. 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

BGR.JPG
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Right(some value, 3) always returns text (even if it looks like a number), 777 in the screen cap is a proper number, so the 2 are not directly comparable. See if this resolves this issue.
VBA Code:
Private Sub VLOOK_UP()
    Dim i As Long, n As Variant
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        n = Right(Worksheets("72 Hr").Cells(i, 7).Value, 3)
        If IsNumeric(n)  Then n = CLng(n)
        Worksheets("72 Hr").Cells(i, 11).Value = _
        Application.WorksheetFunction.VLookup(n, Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub
 
Upvote 0
Solution
Right(some value, 3) always returns text (even if it looks like a number), 777 in the screen cap is a proper number, so the 2 are not directly comparable. See if this resolves this issue.
VBA Code:
Private Sub VLOOK_UP()
    Dim i As Long, n As Variant
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        n = Right(Worksheets("72 Hr").Cells(i, 7).Value, 3)
        If IsNumeric(n)  Then n = CLng(n)
        Worksheets("72 Hr").Cells(i, 11).Value = _
        Application.WorksheetFunction.VLookup(n, Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub
Thank you, I think it works brilliantly, such a little thing makes a big difference. Best wishes!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top