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

Thanks in advance

Rgds
Ray

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
---------------
Jacob Skaria


"swiftcode" wrote:

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

Thanks in advance

Rgds
Ray

Omit R1C1:

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

Regards,
Stefi

€žswiftcode€� ezt Ã*rta:

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

Thanks in advance

Rgds
Ray

There could be a number of reasons the code is failing. The obvious one
is you should use the property formula not formulaR1C1 since you are
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=155039

Microsoft Office Help

Hi Guys,

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

You all have a great week ahead.

Rgds
Ray

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:

Hi Guys,

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

You all have a great week ahead.

Rgds
Ray