VLookup not working in VBA

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
77
Hello,

I'm attempting to do a VLookup in a macro while using the Match function to dynamically determine which column to look in.

I did some testing by sending values to a Msgbox to validate I am passing the correct values.
Ex: MsgBox "Match Value is : " & Match1 <== the value was 13
MsgBox "Lookup Value is : " & Sheet2.Range("A2").Value <== the value was 8

In both both of the above examples, the value that I expected to be passed were. However, the value I am expected to be "Found" using the VLoopup was not correct.

Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("N1:U1"), 0) + 3
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)

Note: Expected results is ==> 0.44444

I'm actually coming back with the number "0".

Section of Table Used to Lookup:
NOTES: (1) the real table does extend beyond to column U and down to row 10
(2) the ONLY difference is that I changed the names. All numerical values are the same.
KLMNO
1813
2DentBulls
33Doe.75.33333.33333
44Zully.6600
55Hill.75.33333.33333
67Smith.66.25.25
78Dent.01.44444
89Vails.8.25.25

<tbody>
</tbody>

Can anyone see what I'm doing wrong? Your help would be appreciated.

Thank you.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,811
Office Version
2007
Platform
Windows
Unfortunately, your example is not complete.
I can't see the values ​​in cells A2, B2 or the values ​​in cells N1:U1

It is not necessary to increase the number of columns.
The following should return the column number starting at 1 column K.
Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("K1:U1"), 0)

So if the searched value is in column O, the result will be 5.
Match1 = 5

Then

FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), 5, False)
FoundValue = .44444

I guess in A2 you have the value "Dent"

If not, you can put all the data they play.
 

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
77
Unfortunately, your example is not complete.
I can't see the values ​​in cells A2, B2 or the values ​​in cells N1:U1

It is not necessary to increase the number of columns.
The following should return the column number starting at 1 column K.
Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("K1:U1"), 0)

So if the searched value is in column O, the result will be 5.
Match1 = 5

Then

FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), 5, False)
FoundValue = .44444

I guess in A2 you have the value "Dent"

If not, you can put all the data they play.
Thank you for your response. I made sure that my code matched what you showed above and, sadly, it did not work. The value that came back was the number "0"

I am providing a "copy" of the code I am using below:
Dim Perc1 As Long
Dim Match1 As Long

Match1 = Application.WorksheetFunction.Match(Sheet2.Range("B2").Value, Sheet2.Range("K1:U1"), 0)
Perc1 = Application.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)

MsgBox ("The Match lookup value is: " & Sheet2.Range("B2")) '==> value found was 13
MsgBox ("The Vlookup value is: " & Sheet2.Range("A2")) '==> value found was 8
MsgBox ("The Match value is: " & Match1) '==> value found was 5
MsgBox ("The lookup value is: " & Perc1) '==> value found was 0

I only have two pages in this worksheet. They are called Sheet1 and Sheet2. Sheet2 is the one that contains the data which I am trying to perform the lookup on.

With hopes you may still be able to discover what might be wrong, here are the answers to your questions:
The value in A2 = 8
The value in B2 = 13
The values in N1:U1 are shown in the table below:
KLMNOPQRSTU
18134735119
2DentHicksZullySmithDoeHillJonesVails
33Doe.75.3333.3333.1111.11110.3333.2222
44Zully.666700.25.25.25.25.25
55Hill.75.3333.3333.3333.6667000
67Smith.6667.25.25.25.25.5.25.25
78Dent.0001.44440.1111.3333.1111.3333.2222
89Vails.8.25.25.125.125.250.5
911Jones.7692.3.3.1.1.30.4
1013Hicks.0001.44440.1111.3333.1111.3333.2222

<tbody>
</tbody>

I've done plenty of Lookups in Excel before but this is not making any sense to me. I am baffled to say the least.

If there is anything else you or anyone else can suggest... I would appreciate it.

Thanks,
Don
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
946
I would never use vlookup in vbA, it is easier and more flexible to do it using varianta rrays and a few loops:
try this:
Code:
' load all the data into variant array
inarr = Range("K1:U10")
vb2 = Sheet2.Range("B2")
va2 = Sheet2.Range("A2")
 For i = 1 To 11
  If inarr(1, i) = vb2 Then
  Exit For ' we have found the column
  End If
 Next i
 For j = 1 To 10
  If inarr(j, 1) = va2 Then
  Exit For ' we have found the row
  End If
 Next j
MsgBox inarr(j, i)
 

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
77
I would never use vlookup in vbA, it is easier and more flexible to do it using varianta rrays and a few loops:
try this:
Code:
' load all the data into variant array
inarr = Range("K1:U10")
vb2 = Sheet2.Range("B2")
va2 = Sheet2.Range("A2")
 For i = 1 To 11
  If inarr(1, i) = vb2 Then
  Exit For ' we have found the column
  End If
 Next i
 For j = 1 To 10
  If inarr(j, 1) = va2 Then
  Exit For ' we have found the row
  End If
 Next j
MsgBox inarr(j, i)
Your approach works GREAT... thanks. I do have one additional question/problem that is directly related. I would like to place the results of the "lookup" into another array but I'm not having any luck doing so. Is that possible? I've provided my code below. I replaced my original VLoopup with the code you provided. That part works great... I love learning new things. Thanks!

Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
Dim X As Long
Dim Y As Long
For X = 1 To 5 Step 1 ' Step thru each row
For Y = 1 To 5 Step 1 ' For each roww, step thru each column

inarr = Range("K1:U10")
vb2 = Sheet2.Range("B2")
va2 = Sheet2.Range("A2")
For i = 1 To 11
If inarr(1, i) = vb2 Then
Exit For ' we have found the column
End If
Next i
For j = 1 To 10
If inarr(j, 1) = va2 Then
Exit For ' we have found the row
End If
Next j

If Y < 5 Then
MyArray(X, Y) = Cells(X + 1, Y).Value 'placing values in a table located in A2:D6 into an array to be used in the future
Else
MyArray(X, Y) = inarr(j, i) 'placing value found using "lookup" in the 5th position of the same array to be used in the future

MsgBox ("This is my array value: " & inarr(j, i)) ' this put the value of .44444 in the message box --- two thumbs up!!
MsgBox ("This is my array value: " & MyArray(X, Y)) ' this resulted in the value of 0 in the message box --- don't understand why??
End If
Next Y
Next X
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
946
Can you describe the whole problem because it is not clear from your code what you are trying to do. Your code would appear to load the same value (inarr(j,i) into all positions in the Myarray up until column 5 where it load the values from column 5 of the worksheet. you aren't doing anything with Myarray after it is loaded, so what result do you expect.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,811
Office Version
2007
Platform
Windows
Thank you for your response. I made sure that my code matched what you showed above and, sadly, it did not work. The value that came back was the number "0"

I am providing a "copy" of the code I am using below:
Dim Perc1 As Long 'Long data type to contain integer numbers
Dim Match1 As Long
The problem is the declaration of the Perc1 variable, since the result can be a number or a text or a decimal. It is solved if you declare the variable as variant.
Unlike the Match variable, the result is a column number, so it can be declared as Long.



Code:
Sub test()
  Dim Match1 As Long,[COLOR=#ff0000] Perc1 As Variant[/COLOR]
  Dim sh2 As Worksheet
  Set sh2 = Sheets("Sheet2")
  Match1 = Application.WorksheetFunction.Match(sh2.[B2], sh2.Range("K1:U1"), 0)
  Perc1 = Application.VLookup(sh2.[A2], sh2.Range("K3:U10"), Match1, 0)
  MsgBox "The lookup value is: " & Perc1
End Sub
-----------------------------
Note: Something that made me noise, is that you mention that you have 2 sheets, but in the code or in the examples you never use sheet1, so I didn't understand why you mentioned sheet1.

So, if everything happens on the same sheet2, you can use the Index function like this:

Code:
Sub test2()  
MsgBox WorksheetFunction.Index([K3:U10], WorksheetFunction.Match([A2], [K3:K10], 0), WorksheetFunction.Match([B2], [K1:U1], 0))
End Sub
Or this:

Code:
Sub test3()
  MsgBox Evaluate("Index(K3:U10,Match(A2,K3:K10,0),Match(B2,K1:U1,0))")
End Sub
------------------------------

If you don't want to use formulas, I recommend the Find method:

Code:
Sub test4()
  Dim f1 As Range, f2 As Range
  Set f1 = Sheets("Sheet2").Range("K1:U1").Find(Range("B2"), , xlValues, xlWhole)
  Set f2 = Sheets("Sheet2").Range("K3:K10").Find(Range("A2"), , xlValues, xlWhole)
  MsgBox Cells(f2.Row, f1.Column)
End Sub
 
Last edited:

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
77
Can you describe the whole problem because it is not clear from your code what you are trying to do. Your code would appear to load the same value (inarr(j,i) into all positions in the Myarray up until column 5 where it load the values from column 5 of the worksheet. you aren't doing anything with Myarray after it is loaded, so what result do you expect.
Here is what I am trying to accomplish:
  • Table A (A2:D6) contains potential combination of matchups for a Doubles Tennis match
  • Table B (K1:U10), shown in a previous example, contains current % of times players played with/against each other
  • As I loop thru the various combinations found in Table A and use the values in Table B to determine which of the potential combinations has played the least with each other. It is this combination that will be used to set up a tennis match.

Previously, I just disclosed the value of A2 = 8 and B2 = 13. Below is the example of what Table A look like, there are no values in row 1:
ABCD
1Player1Player2Player3Player4
281347
384313
485134
5811413
689134

<tbody>
</tbody>

My intent was to do the following:
  1. Loop thru Table A and create an array that consisted of all the potential combination of players
  2. Add to the array created above the value found by the lookup in Table B. However, I will ultimately be performing that same lookup for each of the players in each of the combinations. Ex: In my previous example, we were looking up A2 by using the column found by the player in B2. I would then continue the loop by looking at the column found by the player in C2 and then in D2.
  3. Ultimately, I will want to then find the lowest combined value for each row in Table A.

You are 100% correct in your observation that my code currently only loads the same value (inarr(j,i) into all positions in the Myarray up until column 5. My hope was to simply learn how to find the value and then get it appropriately placed into an array. Once that is done... then I was going to try and expand that to reflect my intentions I expressed above.

Does this make since now? I know what I want.. not sure if it is coming across correctly.

I do appreciate you assistance.

Thank you,
Don
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
946
I think something like this is what you want:
Code:
Sub test2()




Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
Dim X As Long
Dim Y As Long
inarr = Range("K1:U10") ' move this outside the loop so that it only executes once
'load all the players data
players = Range("A2:D6")
For X = 1 To 5 Step 1 ' Step thru each row
For Y = 2 To 5 Step 1 ' For each roww, step thru each column


vb2 = players(X, Y)
va2 = players(X, 1)
For i = 1 To 11
If inarr(1, i) = vb2 Then
Exit For ' we have found the column
End If
Next i
For j = 1 To 10
If inarr(j, 1) = va2 Then
Exit For ' we have found the row
End If
Next j


MyArray(X, Y) = inarr(j, i) 'placing value found using "lookup" in the 5th position of the same array to be used in the future
MsgBox ("This is my array value: " & MyArray(X, Y))
Next Y
Next X
End Sub
 

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
77
I think something like this is what you want:
Code:
Sub test2()




Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
Dim X As Long
Dim Y As Long
inarr = Range("K1:U10") ' move this outside the loop so that it only executes once
'load all the players data
players = Range("A2:D6")
For X = 1 To 5 Step 1 ' Step thru each row
For Y = 2 To 5 Step 1 ' For each roww, step thru each column


vb2 = players(X, Y)
va2 = players(X, 1)
For i = 1 To 11
If inarr(1, i) = vb2 Then
Exit For ' we have found the column
End If
Next i
For j = 1 To 10
If inarr(j, 1) = va2 Then
Exit For ' we have found the row
End If
Next j


MyArray(X, Y) = inarr(j, i) 'placing value found using "lookup" in the 5th position of the same array to be used in the future
MsgBox ("This is my array value: " & MyArray(X, Y))
Next Y
Next X
End Sub
I believe everything is working well except the assignment of the value to the array.

To determine where they problem is, I did the following MsgBox right below the "Next j" code.
MsgBox ("This is my array value: " & inarr(j, i))
This message box produced all the values I would expect to be found and placed into the array.

The following message box produced only "0" each time the message box appeared.
MsgBox ("This is my array value: " & MyArray(X, Y))

So... the problem seems to be the assignment of the value to the array. If this issue can be resolved then I can definitely see how the code you are providing is much more efficient than the "Vlookup" and "Match" I was attempting to use.

Do you have any additional thoughts on this?

Again, thanks for your continued effort. The code you have provided thus far has taught me a lot... and it is appreciated.

Thanks,
Don
 

Forum statistics

Threads
1,077,995
Messages
5,337,616
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top