• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Excel VBA question on VLookup

C

Cattlegod

Diamond Member
If I use VLookup in a cell, I get a correct result. When I have the vlookup function in a VBA loop, I an error that says the value wasn't found. Any idea what could be going wrong? I know I am defining my range correctly and everything. It also isn't inserting a space or anything to get in the way. I'm at a loss.

The code attachment doesn't seem to be working well:

With Sheets("AIMDeck")
Set rng = .Range(.Cells(4, aimloc), .Cells(totalaim + 3, aimloc))
End With


For x = 2 To Sheets("Data").Range("B2") + 1
curraim = Sheets("Total Vehicle Data").Range("A" & x)

If curraim = "Not Required" Then
GoTo nextx
End If

res = Application.VLookup(curraim, rng, 1, False)


If IsError(res) Then
GoTo nextx
Else
missingaim = missingaim - 1
End If
nextx:
Next
 
Have you tried setting the last argument of VLookup to TRUE instead of FALSE? When it is set to FALSE, that allows you to use an unsorted list, but it also only returns an exact match or an error if there is no exact match. TRUE will give an approximate match, but requires a sorted list.

Also, the way that you have it set up, it will return your lookup value (if it is found) since you are returning the value from column 1, which is the same place that you are looking for your value. Maybe that is what you want. However, if your lookup value isn't in the list, you will get an error.
 
Originally posted by: mayest
Have you tried setting the last argument of VLookup to TRUE instead of FALSE? When it is set to FALSE, that allows you to use an unsorted list, but it also only returns an exact match or an error if there is no exact match. TRUE will give an approximate match, but requires a sorted list.

Also, the way that you have it set up, it will return your lookup value (if it is found) since you are returning the value from column 1, which is the same place that you are looking for your value. Maybe that is what you want. However, if your lookup value isn't in the list, you will get an error.
Click to expand...

Just tried setting it to True and the same result happened. Also keep in mind that if I just enter the =vlookup() in a cell, everything works fine with False. I'm interested in finding an exact match or nothing at all.

The loop cycles through a list of numbers on a different sheet. I need to find out how many numbers are missing from this sheet, I don't care what the result is other than if it found a match or not.

I have no idea why this vlookup function isn't finding a result in the range, when I know it is there. I've even looked at the range and had it select the range visually to ensure that I am defining my range correctly. It is crazy.
 
MY GOD, I figured it out finally. arg1 you pass in vlookup( arg1, arg2...) must be a RANGE, it can't be the actaul value

WTF

Anyway, thanks for your help.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top