Using If Statement to avoid a #Div/0!

[dancushing]

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?

 

[GaryAtWork]

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]

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!

 

[dancushing]

Fixed code

Range("E6").FormulaR1C1 = "=IF(R[-1]C<=0,"""",(R[-2]C/R[-1]C)*100)"

 

[GaryAtWork]

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.