formula?

[pam]

(I posted this previously to the "General" newsgroup in
error)


I'm trying to add a formula in a cell which will leave
the cell blank if column A is blank. I use the following:

ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")"


but I get a run-time error (1004). I know the problem is
the quotes in the third argument. I tried replacing the
quotes with zero and it enters zero correctly with no
error. But I need the cell to be blank, not zero, if
column A is blank.
..

 

[David McRitchie]

Hi Pam,
A cell with a formula can never be empty.
You can and did have a formula that returns a null string (empty string)
but that is not the same as being empty.

I presume your macro is setting things up and would not be
run often.

You can create an Event macro based on CHANGE
http://www.mvps.org/dmcritchie/excel/event.htm#change
to set the content to empty such as within an it...then...else
setting the cell value to empty. i.e.
target.value = ""
and the result can be tested with a Worksheet Formula to show
that the cell is indeed empty.
=ISBLANK(B5)

 

[Chip Pearson]

Pam,

Try something like

ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"""")"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com