vlookup values

[dfeld71]

Hello! I have a formula in a macro, which is looking up an account number from spreadsheet A in spreadsheet B, and bringing back the address. If there is not an address on spreadsheet B for a particular account, then the formula is returning a "0", instead of NULL. How do I tell my formula to return a Null instead of a 0? Thank you for your help!

Here is my formula:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],[address.xls]Sheet1!R2C1:R65536C12,6,FALSE)"

[eluehmann]

Maybe if you nest the statement in an if statement like this:

=if(VLOOKUP(RC[-2],[address.xls]Sheet1!R2C1:R65536C12,6,FALSE)=0, "" , VLOOKUP(RC[-2],[address.xls]Sheet1!R2C1:R65536C12,6,FALSE))

you can replace"" with "NULL" if you want

[swatsp0p]

You need to add an IF statement to check for NULL (I assume a blank cell) and return a blank.

=IF(VLOOKUP(RC[-2],[address.xls]Sheet1!R2C1:R65536C12,6,FALSE)="","",VLOOKUP(RC[-2],[address.xls]Sheet1!R2C1:R65536C12,6,FALSE)

HTH

[dfeld71]

swats and elue -
I tried both of your formulas, and in each case my macro errored out. the error I am getting is

Run-time error 1004:
Application-defined or Object-defined error

[swatsp0p]

without seeing your entire macro I don't know, but do you have an "On Error Resume" statement?