Calculating Column and off setting calculated value to another column

[S Himmelrich]

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

 

[Leith Ross]

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