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.

Your kind help is very much needed. Please....

Thanks in advance.

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...

xld
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.

Thanks in advance.

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