Using If Statement to avoid a #Div/0!

dancushing

New Member
Joined
Dec 28, 2005
Messages
3
I am trying to add this formula to my worksheet using VBA.

=IF(E5<=0,"",(E4/E5)*100)

I tried to use this line to do this.

Range("E6").Formula = "=IF(E5<=0,"",(E4/E5)*100)"

I get a 1004 error when I try to run this and I think it has something to do with the quotation marks in the middle of the statement.

I am using this formula in this cell because this cell is part of another formula that computes an average. If there is a #Div/0! then my other formula doesn't work right.

Is there any way to enter this formula using VBA?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GaryAtWork

Board Regular
Joined
Dec 19, 2005
Messages
83
I can't open Excel at the moment, but have you tried typing the formula into a cell with the macro recorder running and seeing what code it writes for you?
 

dancushing

New Member
Joined
Dec 28, 2005
Messages
3
Didn't even think of that. It was getting late when I posted this question. I just tried your suggestion and it recorded it in R1C1 style. It appears to work so I will try to use it that way. Once I get it working right I will post a reply with the fixed code. Thanks!
 

GaryAtWork

Board Regular
Joined
Dec 19, 2005
Messages
83
Again from memory, but I seem to recall a button on the toolbar that appears when you're recording a macro, that changes from relative to absolute references if you'd prefer absolute in your formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,259
Members
412,709
Latest member
Rishu
Top