Calculating Column and off setting calculated value to another column
 

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

Calculating Column and off setting calculated value to anothercolumn
 

On Feb 1, 10:09 am, S Himmelrich 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