question

ConS10-9700 avatar image
0 Votes"
ConS10-9700 asked ConS10-9700 edited

vlookup not choosing correct values

I am using "vlookup" function in vba, but the returned values aren't correct. Does anyone know why I am not getting the right row for the inputted string?

     Dim x As String
        
     Dim rg As Range
     Set rg = ActiveSheet.Range("B16:E20")
        
        
     Dim firstValue As String
     Dim secondValue As String
     Dim thirdValue As String
        
     x = "Violet"
            
     firstValue = Application.WorksheetFunction.VLookup(x, rg, 2)
            
     Debug.Print firstValue
            
            
     secondValue = Application.WorksheetFunction.VLookup(x, rg, 3)
        
     Debug.Print secondValue
            
            
     thirdValue = Application.WorksheetFunction.VLookup(x, rg, 4)
            
     Debug.Print thirdValue


firstValue = 74
secondValue = 75
thirdValue = 76


Cells being used for Vlookup (sorry, I wasn't sure how to bring a proper set of cells into here. Each name/number is in a separate cell)

Magenta 2 3 4
Violet 20 21 22
Burgundy 38 39 40
Sapphire 56 57 58
Turquoise 74 75 76

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

According to documentation, try adding the fourth argument:

 firstValue = Application.WorksheetFunction.VLookup(x, rg, 2, False)


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am now getting an error on that line:

"Run-time error '1004':

Application-defined or object-defined error"

0 Votes 0 ·

Probably the value was not found. If such situations are possible, you can improve your code, intercepting the error using On Error statement.

0 Votes 0 ·
ConS10-9700 avatar image
0 Votes"
ConS10-9700 answered ConS10-9700 edited

ok I found a solution for this. Not sure how it works, but there shouldn't be "WorksheetFunction" section in there:

 firstValue = Application.VLookup(x, rg, 2, False)
 firstValue = Application.VLookup(x, rg, 3, False)
 firstValue = Application.VLookup(x, rg, 4, False)


This correctly returns the values in the row (i.e, 20, 21, 22)

Thank you!!

Edit:

Something to take note about: if you use "True" for the last parameter, the range will need to be in alphabetical order for it to work. See Parameter 4: range_lookup at the url for an example

https://excelmacromastery.com/vba-vlookup/



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.