Error in VLookup

[macro_user]

hi...
i have a doubt regarding VLookup... when would i get #N/A and when would i get #VALUE! ??? i dont know how to differentiate between these 2 errors....

 

[Mike Blackman]

Hi,

An N/A suggests that there is no match, a value error would suggest a problem with the formula. Care to post the formula thats causing issue?

 

[macro_user]

thisworkbook.sheets("Sheet1").activate
lastrow_in_Col_A = range("A65536").End(xlUp).row
    With range("G4:G" & lastrow_in_Col_A)
    .Formula = "=VLOOKUP(F4,'Sheet2'!F:G,2,FALSE)"
    .value = .value
    End With

out of 800 values, 200 values are #N/A and 5 are #VALUE! ... the rest of the values are getting populated from the VLookup formula...

 

[Mike Blackman]

Hi,

I'd suggest;

Dim lRow As Long

lRow = Range("A" & Rows.count).End(xlUp).Row

Range("G4:G" & lRow) = Application.VLookup(Range("F4:F" & lRow), Sheets("Sheet2").Columns("F:G"), 2, 0)

 

[macro_user]

ya i would try this... thank you... but can you tell me wat could've been the error in the previous code and how this piece of code would work better than tat... i need to give an explanation for the change in code... tats why.. Thanx in advance...

 

[macro_user]

hi...
i tried the one which u told me to and still i get the same results... the data for which i get #VALUE! , i dont have the corresponding data to fetch for the VLookup... tats why i get an error... tats fine but i dont understand why i get #N/A seperately and #VALUE! seperately for the same reason...

 

[Mike Blackman]

Hi,

Can you possibly post a few examples of each result and hopefully I'll be able to explain why. I should imagine you will find that my method on the VLOOKUP slightly more efficient over a large number of formula, they are however doing very much the same thing.

 

[macro_user]

hi...
when i looked at the values for which i was doing a VLookup, there was 1 value which was very long, almost 75 characters in length(which dint have a value from the VLookup) for which i got #VALUE!... so would tat be a reason??? bcoz for only tat value i get #VALUE! and for the others which dont have a value, i get #N/A...

 

[Mike Blackman]

Hi,

I guess thats a possibility, not sure what the character limit is on the Lookup Value of a Lookup.