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

--
Message posted from http://www.ExcelForum.com

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

-----Original Message-----
I want to use VLOOKUP in a few cells. The VLOOKUP
function is dependant
on the left adjacent cells. But those cells can also be
empty. If they
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?


---
Message posted from http://www.ExcelForum.com/

.

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)) "

--
HTH,
Dianne

I want to use VLOOKUP in a few cells. The VLOOKUP function is
dependant on the left adjacent cells. But those cells can also be
empty. If they 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?


---
Message posted from http://www.ExcelForum.com/

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:D)"

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 wrote:

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:D)"

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!