Entering Formula with "" within it

[Guest]

I am trying to write the appropriate code to enter a formula along the
following lines into a particular cell in a worksheet.

=IF(Range1= "",Range2, Range1)

I've been advised that I should use coded version of the quotes
incorporating Chr (34) - something like:

strQuote = Chr(34)
ActiveCell.Formula = "=IF(Range1= "& Chr(34) &" ,Range2,Range1)"

but I can't seem to get the syntax quite right.

Any help would be appreciated.

 

[Guest]

Hi

Try using =IF(Range1= """",Range2, Range1)

HTH

 

[Bob Phillips]

Ron,

You need a pair

ActiveCell.Formula = "=IF(Range1= " & Chr(34) & Chr(34)& " ,Range2,Range1)"

or enter directly

ActiveCell.Formula = "=IF(Range1= """" ,Range2,Range1)"

or create a variable

sQuotes = Chr(34) & Chr(34)

ActiveCell.Formula = "=IF(Range1= " & sQuotes & " ,Range2,Range1)"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

Just an alternative...

ActiveCell.Formula = "=IF(ISBLANK(Range1),Range2, Range1)"

 

[Bob Phillips]

But be aware (Ron that is) this formula returns False if there is another
formula such as =IF(cond,val,"") in Range1 whereas Range1="" returns True.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Just an alternative...

ActiveCell.Formula = "=IF(ISBLANK(Range1),Range2, Range1)"

 

[Tushar Mehta]

A technique that I routinely use is to turn on the macro recorder and
enter the formula in a cell. Turn off the recorder and XL will give
you the correct VBA code!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions