PDA

View Full Version : Solved: Formula in VBA code - modification requested to avoid Error

acsishere
05-22-2008, 10:38 AM
Dear Friends,

I have a macro to do a process in which the following code appears.

.FormulaR1C1 = "=SUM(R[-1]C/R[-1]C5*100)"

When it is run, if the value is not there, then it shows the #DIV0! Error.
Can any one help me to avoid this Error.

Generally, in worksheet we apply the = IF(ISERROR(SUM(......) formula. I don't know how to apply this kind of formula with the above code.

acsishere.

grichey
05-22-2008, 10:49 AM
.FormulaR1C1 = "=if(iserror(SUM(R[-1]C/R[-1]C5*100),"",SUM(R[-1]C/R[-1]C5*100)"

This will just leave it blank if it would normally say #div0

grichey
05-22-2008, 10:50 AM
fyi -- it's the exact same application as you mentioned!

if that works, don't forget to mark solved in thread tools...

Bob Phillips
05-22-2008, 11:01 AM
.FormulaR1C1 = "=IF(R[-1]C5=0,"""",SUM(R[-1]C/R[-1]C5*100))"

acsishere
05-22-2008, 11:01 AM
Dear Grichey,

Thanks for your reply. I appied the following code, as you said:

.FormulaR1C1 = "=IF(ISERROR(SUM(R[-1]C/R[-1]C5*100),"",SUM(R[-1]C/R[-1]C5*100))"

It does nothing. I think because of the inverted commas (""). In formula it leaves blank. Whereas in vba the inverted commas are playing different role. Hence, the solution please.

acsishere.

grichey
05-22-2008, 02:19 PM
Sorry as xld mentioned. It should be """"

acsishere
05-23-2008, 08:11 AM
That's nice. Thanks a lot for you both.

Though it is a small code, it relieved me from a lot of trouble.

Thanks again,

acsishere.

grichey
05-23-2008, 08:26 AM
The solved function is at the top under 'thread tools' just above your first post. just fyi