The code below is not calculating the correct column, which should be
"J", not L, where the results are place...not sure what I've done.

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("J1", .Cells(.Rows.Count, "J").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0


If myRng Is Nothing Then
MsgBox "no constants"
Exit Sub
End If


For Each myArea In myRng.Areas
myFormula = "=sum(r[-1]c:r[-" & myArea.Cells.Count & "]c)"
Set FormCell = myArea.Cells(myArea.Cells.Count).Offset(1,
0)
With FormCell
.FormulaR1C1 = myFormula
.Offset(0, 2).Value = .Value
.Offset(0, 2).Font.Bold = True
.FormulaR1C1 = ""
End With
Next myArea
End With

On Feb 1, 10:09 am, S Himmelrich <himmelr...@gmail.com> wrote:
> The code below is not calculating the correct column, which should be
> "J", not L, where the results are place...not sure what I've done.
>
> With wks
> Set myRng = Nothing
> On Error Resume Next
> Set myRng = .Range("J1", .Cells(.Rows.Count, "J").End(xlUp)) _
> .Cells.SpecialCells(xlCellTypeConstants)
> On Error GoTo 0
>
> If myRng Is Nothing Then
> MsgBox "no constants"
> Exit Sub
> End If
>
> For Each myArea In myRng.Areas
> myFormula = "=sum(r[-1]c:r[-" & myArea.Cells.Count & "]c)"
> Set FormCell = myArea.Cells(myArea.Cells.Count).Offset(1,
> 0)
> With FormCell
> .FormulaR1C1 = myFormula
> .Offset(0, 2).Value = .Value
> .Offset(0, 2).Font.Bold = True
> .FormulaR1C1 = ""
> End With
> Next myArea
> End With

Hello S Himmelrich,

Your new range myRng is only 1 column wide. It holds the contents of
column "J". Your code below Offsets from this address to 2 columns
over. This places you back on the worksheet in column "L".

With FormCell
.FormulaR1C1 = myFormula
.Offset(0, 2).Value = .Value <-----------
.Offset(0, 2).Font.Bold = True <-----------
.FormulaR1C1 = ""
End With
Next myArea
End With

Sincerely,
Leith Ross