Calculating column 'J' and offsetting result in column 'L' on nextblank row (continue to end of shee

[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

 

[Don Guillett]

Methinks you are making this harder than necessary. What are you trying to
do?

 

[S Himmelrich]

I sure I am....but this code was working at one point, however not
anymore so I'm back to square one.


Group Name Total
NBK Scott 10
Diane 18
Jim 15
43 (<this result)
SCS Tony 7
Judy 13
Bob 21
41(<this result)

 

[Peter T]

FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

 

[S Himmelrich]

You are correct, and that is the result, however I'm not getting my
subtotal on that line.

FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

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- Hide quoted text -

- Show quoted text -

 

[Peter T]

In your OP you say you don't want the results in col-L, two columns to right
of J. Yet that is what your code does with .Offset(0,2), and you agree
that's what it does. So now you are saying it goes into the correct column
but into the wrong row, at least I think that's what you are saying.

Your code places subtotals 1 row below & 2 columns to right of each area.
It's difficult to understand what you want but that's what your code does.

Regards,
Peter T

You are correct, and that is the result, however I'm not getting my
subtotal on that line.

FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

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- Hide quoted text -

- Show quoted text -

 

[S Himmelrich]

Your explaination is correct, however the issues is I'm getting '0' on
subtotals....and I don't know why.

In your OP you say you don't want the results in col-L, two columns to right
of J. Yet that is what your code does with .Offset(0,2), and you agree
that's what it does. So now you are saying it goes into the correct column
but into the wrong row, at least I think that's what you are saying.

Your code places subtotals 1 row below & 2 columns to right of each area.
It's difficult to understand what you want but that's what your code does.

Regards,
Peter T


You are correct, and that is the result, however I'm not getting my
subtotal on that line.

FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

news:[email protected]...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Peter T]

Let me get this right, values (subtotals) go into the correct row and the
correct column, the question does not relate to where the values go ?

But you get zero instead of the correct subtotal. With constants in a number
of areas in col-J, running your code I get correct area subtotals, each
offset(1,2) below and to right last cell in each area.

For testing comment this line
' .FormulaR1C1 = ""

Regards,
Peter T


Your explaination is correct, however the issues is I'm getting '0' on
subtotals....and I don't know why.

In your OP you say you don't want the results in col-L, two columns to right
of J. Yet that is what your code does with .Offset(0,2), and you agree
that's what it does. So now you are saying it goes into the correct column
but into the wrong row, at least I think that's what you are saying.

Your code places subtotals 1 row below & 2 columns to right of each area.
It's difficult to understand what you want but that's what your code does.

Regards,
Peter T


You are correct, and that is the result, however I'm not getting my
subtotal on that line.

FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

news:[email protected]...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[S Himmelrich]

Now I have three responses, on column J=sum(r[-1]c:r[-83]c)
K=#VALUE!
L=0

L is still 0 where I'm hoping to have the subtotal of Column J (above)

Let me get this right, values (subtotals) go into the correct row and the
correct column, the question does not relate to where the values go ?

But you get zero instead of the correct subtotal. With constants in a number
of areas in col-J, running your code I get correct area subtotals, each
offset(1,2) below and to right last cell in each area.

For testing comment this line
' .FormulaR1C1 = ""

Regards,
Peter T


Your explaination is correct, however the issues is I'm getting '0' on
subtotals....and I don't know why.

In your OP you say you don't want the results in col-L, two columns to right
of J. Yet that is what your code does with .Offset(0,2), and you agree
that's what it does. So now you are saying it goes into the correct column
but into the wrong row, at least I think that's what you are saying.

Your code places subtotals 1 row below & 2 columns to right of each area..
It's difficult to understand what you want but that's what your code does.

Regards,
Peter T

"S Himmelrich" <[email protected]> wrote in message

You are correct, and that is the result, however I'm not getting my
subtotal on that line.

FormCell.Offset(0, 2) is two columns to the right.
Regards,
Peter T

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- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Peter T]

I get the impression you are using code that you found from some source
without understanding what it does.

Examine the formula in your K cell. If your K cell is say K86 the formula
should be
=SUM(J3:J85)
Now look for any cells in J3:J85 that also return #VALUE!

Regards,
Peter T

Now I have three responses, on column J=sum(r[-1]c:r[-83]c)
K=#VALUE!
L=0

L is still 0 where I'm hoping to have the subtotal of Column J (above)

Let me get this right, values (subtotals) go into the correct row and the
correct column, the question does not relate to where the values go ?

But you get zero instead of the correct subtotal. With constants in a number
of areas in col-J, running your code I get correct area subtotals, each
offset(1,2) below and to right last cell in each area.

For testing comment this line
' .FormulaR1C1 = ""

Regards,
Peter T


Your explaination is correct, however the issues is I'm getting '0' on
subtotals....and I don't know why.

In your OP you say you don't want the results in col-L, two columns to right
of J. Yet that is what your code does with .Offset(0,2), and you agree
that's what it does. So now you are saying it goes into the correct column
but into the wrong row, at least I think that's what you are saying.

Your code places subtotals 1 row below & 2 columns to right of each area.
It's difficult to understand what you want but that's what your code

does.

Regards,
Peter T

"S Himmelrich" <[email protected]> wrote in message

You are correct, and that is the result, however I'm not getting my
subtotal on that line.

FormCell.Offset(0, 2) is two columns to the right.
Regards,
Peter T

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- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[S Himmelrich]

Thank you for your guidance on this effort. You are correct I am
using code that was from another source. I've further taken a look at
this project and now created simpler yet a search and replace on the
column to correct the =sum statement to correctly provide me the
totals. Yes, I know that is literally placing a fix over the problem,
but considering I don't seem to find simple examples of creating sub-
totals on the next blank line I'm needing to put the topic at a
close. I'm hopefully to learn how to do this in the near future.
Thank you again for your guidance.

Scott