VBA Formula Error

[Jetx227]

Hi Guys!
I'm just trying to make a for loop that adds a formula to a cell and I keep running an error on that line so I think I typed it in wrong, and I'm pretty sure it has to do with some quotation marks I used, but I've used VBA to create a formula in a sheet before so if someone could just look it over and let me know where I messed up and why that would be greatly appreciated. Thanks!

This is the line:

Worksheets(1).Range("J" & CStr(Row)).Formula = "=IF(OR(C" & CStr(Row) & "= """",(C" & CStr(Row) & "-I" & CStr(Row) & ")=0, ""NA"","" SUM(E" & CStr(Row) & "*0.1*1,F" & CStr(Row) & "*0.2*2,G" & CStr(Row) & "0.3*3, H" & CStr(Row) & "0.4*4)/SUM(E" & CStr(Row) & "*0.1, F" & CStr(Row) & "*0.2, G" & CStr(Row) & "*0.3, H" & CStr(Row) & "*0.4))"

This is an example of what the formula should look like after:

=IF(OR(C17="", (C17-I17)=0), "NA", SUM(E17*0.1*1,F17*0.2*2,G17*0.3*3,H17*0.4*4)/SUM(E17*0.1,F17*0.2,G17*0.3,H17*0.4))

 

[mikerickson]

Your code is missing the ) after =0

 

[Jetx227]

Your code is missing the ) after =0

Thanks! I missed that, but I'm still running the Application-defined or object-defined error on that line when I run it. Any other ideas as what what could cause it?

 

[kweaver]

One thing I noticed is that you could be dividing by 0 if the divisor sum = 0.

 

[Jetx227]

One thing I noticed is that you could be dividing by 0 if the divisor sum = 0.

Thats why I used an IF statement with the "NA" condition at the beginning. The cell that it's referencing is a subtotal of sorts. So if that is 0 then sum would never run. Thanks for the input though!

 

[Fluff]

How about

    Worksheets(1).Range("J" & CStr(Row)).FormulaR1C1 = _
        "=IF(OR(RC[-7]="""", (RC[-7]-RC[-1])=0), ""NA"", SUM(RC[-5]*0.1*1,RC[-4]*0.2*2,RC[-3]*0.3*3,RC[-2]*0.4*4)/SUM(RC[-5]*0.1,RC[-4]*0.2,RC[-3]*0.3,RC[-2]*0.4))"

I would also advise against using VBA keywords (such as Row) for the names of variables.

 

[Jetx227]

How about

    Worksheets(1).Range("J" & CStr(Row)).FormulaR1C1 = _
        "=IF(OR(RC[-7]="""", (RC[-7]-RC[-1])=0), ""NA"", SUM(RC[-5]*0.1*1,RC[-4]*0.2*2,RC[-3]*0.3*3,RC[-2]*0.4*4)/SUM(RC[-5]*0.1,RC[-4]*0.2,RC[-3]*0.3,RC[-2]*0.4))"

I would also advise against using VBA keywords (such as Row) for the names of variables.

Works perfect! I've never seen the FormulaR1C1 before, will definitely use this from now on. And yeah, its not actually "Row", I just changed it in the example so it would be clear what I was using for that variable value. Thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.