Excel 2003 isEMPTY is causing DIV/0 error 1

[vba317]

I have a vba script that is causing a DIV/0 error. I have tried different approaches with no success. I am hoping to get some assistance. If I go on the spreadsheet and use the formula =IF(E6=0,0,E6/D6) this works on the spreadsheet so I tried to put this in a new formula I am now getting a run-time error 1004. application defined or object -defined error. For this example iTRow is 6.

Tom

'Original code
With goXL.ActiveSheet
'.Cells(iTRow, 6).Formula = "=if(ISBLANK(E" & (iTRow) & "),0,E" & (iTRow) & "/D" & (iTRow) & ")"

'2nd attempt
.Cells(iTRow, 6).Formula = "=if(E" & (iTRow) & "=0" & "),0,E" & (iTRow) & "/D" & (iTRow) & ")"
End with

 

[Andrzejek]

Did you try (iTRow is 6):
[tt]
.Cells(iTRow, 6).Formula = "=if(E" & iTRow & "=0,0,E" & iTRow & "/D" & iTRow & ")"
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vba317]

Andy,
I just tried your solution and I get the error 1004

.Cells(iTRow, 6).Formula = "=if(E" & iTRow & "=0" & "),0,E" & iTRow & "/D" & iTRow & ")"

 

[PHV]

Anyway, I'd test D6 instead of E6 as you compute E6/D6 ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VulcanJedi]

can you post a sheet w/ the values provided?

Tranpkp

 

[vba317]

I tried to change the formula to iserror and change the cell reference to the denominator I am getting the #DIV/0 error. I will posting the sheet.

Cells(iTRow, 6).Formula = "=if(ISERROR(D" & (iTRow) & "),0,E" & (iTRow) & "/D" & (iTRow) & ")"

 

[vba317]

Sorry, I don't remember how to attach an excel sheet to this thread.

Tom

 

[vba317]

After much more experimentation I finally got something that works. I get 0 in the cell instead of the DIV/0 error.

I changed the formula to this:

.Cells(iTRow, 6).Formula = "=if(E" & iTRow & "=0,0,E" & iTRow & "/D" & iTRow & ")"

 

[Andrzejek]

Which is exactly what I gave you.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vba317]

You are right Andy. I misread your code.

Tom

 

[zelgar]

You do realize the reason why you're getting an error is when the value in Col D is empty or equal to 0, not the value in Col E? Additionally, is there any benefit for you to have the formula in the cell versus the result? Since you are doing this with a macro, the macro could do the calculation and put the results in the cell. Having both the macro & the formula in the cell only makes the spreadsheet bigger (in terms of memory) and may result in taking longer to run the macro or whenever you manipulate your spreadsheet (e.g., it's having to recalculate every formula)

 

[SkipVought]

Reflects my question, too. Why use VBA at all?

If you were to use the Data > List feature (which has been greatly enhanced in Excel 2007+ versions as the Structured Table feature), Excel will propagate your formulas automatically as you add new rows do data. No VBA required! And I'm a huge VBA fan!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vba317]

Zelgar,
In this case the manager wants the formula in the spreadsheet.
Tom

 

[SkipVought]

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]