Need help with VLOOKUP

[DeeJay]

I want to use VLOOKUP in a few cells. The VLOOKUP function is dependan
on the left adjacent cells. But those cells can also be empty. If the
are, VLOOKUP returns the value #N/A. I want to suppress that result.

I was thinking of using an IF statement like this:

ActiveCell.FormulaR1C1
"=IF(RC[-1]="""","""",=VLOOKUP(B13,rmain!$A$1:$B$2000,2))"


But when I add this I get the following message:

"Runtime error 1004:
Application-define or object-defined error"

What am i doing wrong

 

[Brad]

In the worksheet function use IsError and evaluate the
result.

=IF(ISERROR(VLOOKUP(B13,Rmain!
$A$1:$B$2000,2,FALSE)),"None",VLOOKUP(B13,Sheet2!
$A$1:$B$2000,2,FALSE))

If it's any of the Excel error values #DIV/0 or #N/A,
IsError() returns true.

HTH
-Brad

 

[Dianne]

You're mixing R1C1 with A1 style references, so I imagine that's causing
some problems. Also, you don't need the equal sign in front of VLOOKUP.
Try this:

ActiveCell.Formula = _
"=IF(" & ActiveCell.Offset(0, -1).Address & _
"="""","""",VLOOKUP(B13,rmain!$A$1:$B$2000,2))"

 

[Elinor Hartman]

I am trying to do a similar thing.
I want the lookup value to be a concatenation of two cells using the
following code that is crashing on run.

ActiveCell.FormulaR1C1 =
"=LOOKUP(E1&""000""&TEXT(D4,""D-MMM-YYYY""),autoReserves!A:A,autoReserve
s!D)"

Please post the reply as I cannot recieve emails. Thanks for your effort
and knowledge

 

[Dave Peterson]

If you're using formular1c1, you have to write your formula using R1C1 reference
style.
But it looks like just changing to:

activecell.formula = ....

should work.

 

[Elinor Hartman]

Please post the reply as I cannot recieve emails. Thanks for your effort
and knowledge