Hi guys


ActiveCell.FormulaR1C1 =
"IF(VLOOKUP(RC[2],Sup_list,1,FALSE)="#N/A","No","Yes")"

This is part of my macro, however the "#" messes up my macro.
Anyone know how to get around this...?


--
Ctech


------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=540629

Hi Ctech,

Is'nt rather the quote inside the quote which creates th trouble ...
give a try to :
""#NA"" ...

HTH
Cheers
Carim

It is not the # that is causing the problem but the fact that you have a
text string containg ". To overcome this you need to use multiple "

try

ActiveCell.FormulaR1C1 = "IF(VLOOKUP(RC[2],Sup_list,1,FALSE)=""" &
"#N/A" & """, """ & "No" & """,""" & "Yes" & """)"


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=540629

I think you're getting messed up by the double quotes and the error:

I think you want:

ActiveCell.FormulaR1C1 _
= "=IF(iserror(VLOOKUP(RC[2],Sup_list,1,FALSE)),""No"",""Yes"")"

or since you're just checking for existence in one column:

ActiveCell.FormulaR1C1 _
= "=IF(iserror(match(RC[2],Sup_list,0)),""No"",""Yes"")"

And don't forget the equal sign to start your formula.

Ctech wrote:

Hi guys

ActiveCell.FormulaR1C1 =
"IF(VLOOKUP(RC[2],Sup_list,1,FALSE)="#N/A","No","Yes")"

This is part of my macro, however the "#" messes up my macro.
Anyone know how to get around this...?

--
Ctech

------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=540629

--

Dave Peterson