Formula Cell Error

[swiftcode]

Hi all,

i have a problem with the following code, would appreciate if anyone can
give me some help on this. Basically everything works fine except for this
line:

Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")"

The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)".

---------------------------------------------------------------------------------------------
Sub Test()

For i = 2 To 100 Step 1
Cells(i, 1).Select
If Cells(i, 1).Interior.ColorIndex = 2 Then
StartCount = ActiveCell.Row

ElseIf Cells(i, 1).Interior.ColorIndex = 37 Then
EndCount = ActiveCell.Row
Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")"

End If
Next i

End Su
----------------------------------------------------------------------------------------

Thanks in advance

Rgds
Ray

 

[Jacob Skaria]

Try the below. Your formula is not in R1C1 syntax

Cells(i, 12).Formula = "=SUM(J" & StartCount & ":J" & EndCount & ")"

If this post helps click Yes

 

[Stefi]

Omit R1C1:

Cells(i, 12).Formula = "=SUM(J" & StartCount & ":J" & EndCount & ")"

Regards,
Stefi

„swiftcode†ezt írta:

 

[joel]

There could be a number of reasons the code is failing. The obvious on
is you should use the property formula not formulaR1C1 since you ar
using column J. I made some other changes.


Sub Test()

StartCount = 0
EndCount = 0
StartRow = 2
EndRow = 100
For i = StartRow To EndRow Step 1
If Range("A" & i).Interior.ColorIndex = 2 Then
StartCount = i
End If
If Range("A" & i).Interior.ColorIndex = 37 Then
EndCount = i
End If
If StartCount <> 0 And EndCount <> 0 Then
Range("L" & i).Formula = _
"=SUM(J" & StartCount & ":J" & EndCount & ")"
End If
Next i
If StartCount = 0 Or EndCount = 0 Then
MsgBox ("Did not find one of the colors")
End If

End Su

 

[swiftcode]

Hi Guys,

Thank you very much for all you solutions, it solved my problem.

You all have a great week ahead.

Rgds
Ray

 

[Stefi]

You are welcome! Thanks for the feedback!

Sorry for the late answer but I've just now realized that my e-mail
notification doesn't work.


--
Regards!
Stefi



„swiftcode†ezt írta: