Need help with VLOOKUP
 

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

Need help with VLOOKUP
 

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/

.

Need help with VLOOKUP
 

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/

Need help with VLOOKUP
 

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!

Need help with VLOOKUP
 

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

Need help with VLOOKUP
 

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!