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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S Himmelrich" wrote in message
...
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

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)

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

Regards,
Peter T

"S Himmelrich" wrote in message
...
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

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

On Feb 4, 12:09*pm, "Peter T" <peter_t@discussions wrote:
FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

"S Himmelrich" wrote in message

...



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 -

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" wrote in message
...
You are correct, and that is the result, however I'm not getting my
subtotal on that line.

On Feb 4, 12:09 pm, "Peter T" <peter_t@discussions wrote:
FormCell.Offset(0, 2) is two columns to the right.

Regards,
Peter T

"S Himmelrich" wrote in message

...



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 -

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

On Feb 4, 2:55*pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message

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

On Feb 4, 12:09 pm, "Peter T" <peter_t@discussions wrote:



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

Regards,
Peter T

"S Himmelrich" wrote in message

...

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 -

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


"S Himmelrich" wrote in message
...
Your explaination is correct, however the issues is I'm getting '0' on
subtotals....and I don't know why.

On Feb 4, 2:55 pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message

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

On Feb 4, 12:09 pm, "Peter T" <peter_t@discussions wrote:



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

Regards,
Peter T

"S Himmelrich" wrote in message

...

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 -

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)



On Feb 4, 3:43*pm, "Peter T" <peter_t@discussions wrote:
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

"S Himmelrich" wrote in message

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

On Feb 4, 2:55 pm, "Peter T" <peter_t@discussions wrote:



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" wrote in message

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

On Feb 4, 12:09 pm, "Peter T" <peter_t@discussions wrote:

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

Regards,
Peter T

"S Himmelrich" wrote in message

...

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 -

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

"S Himmelrich" wrote in message
...
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)



On Feb 4, 3:43 pm, "Peter T" <peter_t@discussions wrote:
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

"S Himmelrich" wrote in message

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

On Feb 4, 2:55 pm, "Peter T" <peter_t@discussions wrote:



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" wrote in message

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

On Feb 4, 12:09 pm, "Peter T" <peter_t@discussions wrote:

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

Regards,
Peter T

"S Himmelrich" wrote in message

...

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 -

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