VLookup Issue

[Tooch81]

Since you all were such a big help with my problem yesterday, I have another question for you.

I have a v-lookup statement in my macro and it returns values and #N/A when there is no value, just as expected. The problem is, once I try to use the cells that now have data in them from the v-lookup the macro crashes when I reach a cell with #N/A in it. How can I get the v-lookup to return "" or "0" instead of #N/A? I've tried replacing it but no matter what I do, once I get to the #N/A value in a cell it crashes.

 

[CharlesChuckieCharles]

=if(isna(vlookup("blah blah")),"",vlookup("blah blah"))

ISNA is the test for #N/A

("blah blah") ia obviously your current parameters

 

[Tooch81]

So I'm an idiot and keep getting more errors. Can you help me with teh code;

ActiveCell.Formula = "=VLOOKUP(" & Cells(count3 + 26, count2).Address & ",SumData!$A$1:$Z$1000,5,FALSE)"

 

[CharlesChuckieCharles]

Ah

did not read initial post carefully enough

VLOOKUP is not good in VBA

The entry you just posted looks great to me

Check your counts because you get errors if reference outside your table in vlookup

 

[Tooch81]

Ah

did not read initial post carefully enough

VLOOKUP is not good in VBA

The entry you just posted looks great to me

Check your counts because you get errors if reference outside your table in vlookup

the vlookup wrote works fine, but I'm trying to omit the #N/A's because they are causing problems down the road.

 

[CharlesChuckieCharles]

"=VLOOKUP(" & Cells(count3 + 26, count2).Address & ",SumData!$A$1:$Z$1000,5,FALSE)"

this bit Cells(count3 + 26, count2) can be wrapped in an ISNA as in IF(ISNA(Cells(count3 + 26, count2)),0,Cells(count3 + 26, count2))

so you could check that first a trap that possibility

"=IF(ISNA(VLOOKUP(" & Cells(count3 + 26, count2).Address & ",SumData!$A$1:$Z$1000,5,FALSE))," & """""" & 
", VLOOKUP(" & Cells(count3 + 26, count2).Address & ",SumData!$A$1:$Z$1000,5,FALSE))" 
[code]
 
(that's six dbl quotes "   in the middle )

 

[Tooch81]

AMAZING!

Thank you so much.