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
(E-Mail Removed)
"S Himmelrich" <(E-Mail Removed)> wrote in message
news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
> 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" <himmelr...@gmail.com> wrote in message
>
> news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
>
>
>
> > 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" <(E-Mail Removed)> wrote in message
news:8e906854-876b-46a7-a5b8-(E-Mail Removed)...
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" <himmelr...@gmail.com> wrote in message
>
> news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
>
>
>
> > 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" <himmelr...@gmail.com> wrote in message
>
> news:8e906854-876b-46a7-a5b8-(E-Mail Removed)...
> 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" <himmelr...@gmail.com> wrote in message
>
> >news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
>
> > > 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" <(E-Mail Removed)> wrote in message
news:fda8542b-ba56-436b-a8da-(E-Mail Removed)...
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" <himmelr...@gmail.com> wrote in message
>
> news:8e906854-876b-46a7-a5b8-(E-Mail Removed)...
> 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" <himmelr...@gmail.com> wrote in message
>
> >news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
>
> > > 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" <himmelr...@gmail.com> wrote in message
>
> news:fda8542b-ba56-436b-a8da-(E-Mail Removed)...
> 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" <himmelr...@gmail.com> wrote in message
>
> >news:8e906854-876b-46a7-a5b8-(E-Mail Removed)...
> > 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" <himmelr...@gmail.com> wrote in message
>
> > >news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
>
> > > > 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" <(E-Mail Removed)> wrote in message
news:91bd0d03-e889-4c25-8c44-(E-Mail Removed)...
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" <himmelr...@gmail.com> wrote in message
>
> news:fda8542b-ba56-436b-a8da-(E-Mail Removed)...
> 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" <himmelr...@gmail.com> wrote in message
>
> >news:8e906854-876b-46a7-a5b8-(E-Mail Removed)...
> > 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" <himmelr...@gmail.com> wrote in message
>
> > >news:57e535d1-d24f-4e2e-8341-(E-Mail Removed)...
>
> > > > 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 -