VlookUp function

[yalmallo]

Hi,

When I used a Vlookup function with VBA, I've got #N/A results, does any body knox why?

The code that I used is :

Please Login or Register  to view this content.

[tigertiger]

Hi,

When I used a Vlookup function with VBA, I've got #N/A results, does any body knox why?
The code that I used is :

Range("Q2", Range("Q2").End(xlDown)) = Application.VLookup(Range("F2", Range("F2").End(xlDown)), Range("$H$2:$K$2", Range("$H$2:$K$2").End(xlDown)), 3, False)

Change to

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[rscsmith]

Hi yalmallo

Generally, #NA means the value doesn't exist in the list. Can you upload a sample sheet so we can see what the code is interacting with? Without this it is difficult to see why the code won't work.

In the meantime - things to check:
- I think the first value in the Vlookup command is incorrect. Typical VLookup command: VLOOKUP(F2, H2:K7,3,False). You have a multi-cell range as the first argument. Your vlookup looks like: VLOOKUP(F2:F7, H2:K7,3,False).
- Does the range contain the value you are looking for? Beware of rogue spaces, etc.
- Is the lookup table range being correctly selected? Are there any blank rows/cells that could interrupt the End(xlDown)?

Cheers, Rob.

[blane245]

If there is nothing on the Q column when you run this, you will get a sequence of #N/As after the valid results. Is that what you are seeing? Since you want the update for the Q column to stop at the end of the F column you might try this

Please Login or Register  to view this content.

[yalmallo]

Hi yalmallo

Generally, #NA means the value doesn't exist in the list. Can you upload a sample sheet so we can see what the code is interacting with? Without this it is difficult to see why the code won't work.

In the meantime - things to check:
- I think the first value in the Vlookup command is incorrect. Typical VLookup command: VLOOKUP(F2, H2:K7,3,False). You have a multi-cell range as the first argument. Your vlookup looks like: VLOOKUP(F2:F7, H2:K7,3,False).
- Does the range contain the value you are looking for? Beware of rogue spaces, etc.
- Is the lookup table range being correctly selected? Are there any blank rows/cells that could interrupt the End(xlDown)?

Cheers, Rob.

I think that the problem is that I have some empty cells in the xls file. So, VLOOKUP could not support empty cells. So I think That I have to do a éLOOPé or something like this!!!

[yalmallo]

If there is nothing on the Q column when you run this, you will get a sequence of #N/As after the valid results. Is that what you are seeing? Since you want the update for the Q column to stop at the end of the F column you might try this

Please Login or Register  to view this content.

this code gives lastrow = 3. and it has to have a value of 4505 at least!

[yalmallo]

Change to

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the first code takes a lot of time and it doesn't stop.

the second one doesn't work!!!

[yalmallo]

This one works:


Dim lastRow As Long
lastRow = Range("D2").End(xlDown).Row
Range("Q2:Q" & lastRow) = _
Application.VLookup(Range("F2:F" & lastRow), _
Range("$H$2:$K$2", Range("$H$2:$K$2").End(xlDown)), 3, False)




I've juste changed the column of the lastrow to be D2. And it works.

I think that the problem becomes from that the column F2 has the fourth cell empty, VBA stops here and returns lastrow=3.

[blane245]

Yes, empty cells definately mess up End(xlDown). You might try the UsedRange property. It is a bit more reliable.

[yalmallo]

YES.


Thanks.