format cell based on condition.

[Question_123]

Hi all,

I am trying to format a cell so I dont get the divi by zero error. Im
my macro I am processing one sheet into many smaller sheets. I am
applying format where needed on the new sheets.

However I want to get rid of the div by zero displaying when there is
no value.

This is the snipet of code that works fine.

'Insert formula
wsReport.Range("H6").Formula = "=Sum(G6/I6)"
wsReport.Range("H26").Formula = "=Sum(G27/I27)"
wsReport.Range("H29").Formula = "=Sum(G29/I29)"

However it produces the #DIV/0! when the cell is 0. I want to make it
display
nothing if this is the case ... so i tried this, which works fine if i
key it directly into the cell. In my macro I get run time errors. I
am very new to this so please dont leave out anything you might take
for granted. I am using excel 97.

'Insert Formula
wsReport.Range("H6").Formula = "=IF(I6<>0,SUM(G6/I6),"")"
wsReport.Range("H26").Formula = "=IF(I27<>0,SUM(G27/I27),"")"
wsReport.Range("H29").Formula = "=IF(I29<>0,SUM(G29/I29),"")"

Thanks all.

 

[Bob Umlas]

You need to double up on the quotes inside the formula. Change
"=IF(I6<>0,SUM(G6/I6),"")"
to
"=IF(I6<>0,SUM(G6/I6),"""")"
etc.

Bob Umlas
Excel MVP

 

[Question_123]

Thanks Bob, that worked great.

You need to double up on the quotes inside the formula. Change
"=IF(I6<>0,SUM(G6/I6),"")"
to
"=IF(I6<>0,SUM(G6/I6),"""")"
etc.

Bob Umlas
Excel MVP