Thanks for all the help Matt!!! I used your idea and got it working.

Code below:

Cells.Find(What:="(A)*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 5).Select

Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R11C:R[-2]C)"

Dim rngStart As Range
Dim rngEnd As Range

Selection.End(xlDown).Select
Set rngEnd = ActiveCell
Selection.End(xlDown).Select
Set rngStart = ActiveCell
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = Application.Sum(Range(rngStart, rngEnd))

This line will do the sum:
ActiveCell.FormulaR1C1 = Application.Sum(Range(rngStart, rngEnd))

Basically, the last line in the code will do the sum, and the value is hard
coded in the cell. I'd really like to see the =sum() function in the cell.

I tried this, but couldn't get it to work:
ActiveCell.FormulaR1C1 = "=SUM(" & rngStart & ":" & rngEnd & ")"

Any ideas about what I'm doing wrong?

Thanks!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Matthew Herbert" wrote:

On Aug 14, 11:06 am, ryguy7272
wrote:
If I record a macro, I can get below the range, and then hit Ctrl + Shift +
up arrow. I get this code as a result:
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-2]C)"

Excel knows to stop at the end of the used range, but the R[-13] is a
hard-code solution, so that won't work. Is there a VBA equivalent to Ctrl +
Shift + Up?

Thanks,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"Matthew Herbert" wrote:
On Aug 14, 10:23 am, ryguy7272
wrote:
Ive got a variable little groups of cells that need to be summed. They can
appear higher or lower on a sheet and the only way I can think of identifying
the groups is as follows. Look for an indicator in Column A, which is
actually €˜(A), then move right 5 cells and up 2 cells, but the group of
cells that need to be summed is variable.

Please look at my code and offer suggestions:

Cells.Find(What:="(A)*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 5).Select
ActiveCell.Offset(-2, 0).Select

RowCount = 12
ActiveCell.FormulaR1C1 = "=SUM(R[" & -RowCount & "]C:R[-2]C)"

The number of rows that I need to sum will not always be 12, it could be
anything. In my current example, the array goes from F132:F143.

Any ideas on how to do this?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..

Ryan,

If there is no way to determine the row count then you will be out of
luck; however, if you have some other marker (similar to your "(A)",
for example) then it won't be much of a problem. The computer simply
works on your behalf, so if you can observe a repeatable system for
determining the row count, then it can be coded.

Best,

Matthew Herbert- Hide quoted text -

- Show quoted text -

Ryan,

Yes, there is an equivalent to Ctrl+Shift+Arrow Key. The equivalent
is the End method of the Range object. For example, you can have Range
("A1").End(xlDown), wherein the xlDirection can be xlDown, xlUp,
xlLeft, or xlRight. Also, maybe you like R1C1 notation, but I find it
less intuative than A1 notation. Though the macro recorder records in
R1C1 notation, you don't have to code in R1C1.

I did some guess work below on where you want the formula to actually
reside, so the message boxes will let you see how the macro is
behaving. Adjust the code as you will and feel free to take out the
message boxes. (If you don't like the MsgBox popup then use
Debug.Print in place of MsgBox. Debug.Print will print to the
Immediate Window -- View | Immediate Window). Also, be sure to step
through your code line by line via Debug | Step Into (F8) -- simply
hit F8 multiple times.

Best,

Matt

Dim rngStart As Range
Dim rngEnd As Range

Set rngStart = Cells.Find(What:="(A)*", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Offset(-2, 5)

MsgBox "Starting range: " & rngStart.Address

Set rngEnd = rngStart.End(xlUp)

MsgBox "Ending range: " & rngEnd.Address

MsgBox "Formula range: " & rngEnd.Offset(-1, 0).Address(False, False)
& vbLf & _
"Sum range : " & Range(rngStart, rngEnd).Address(False,
False)

rngEnd.Offset(-1, 0).Formula = "=SUM(" & Range(rngStart,
rngEnd).Address(False, False) & ")"

On Aug 16, 5:59*pm, ryguy7272
wrote:
Thanks for all the help Matt!!! *I used your idea and got it working. *

Code below:

* * Cells.Find(What:="(A)*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
* * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
* * * * False, SearchFormat:=False).Activate
* * * * ActiveCell.Offset(0, 5).Select

* * Selection.End(xlUp).Select
* * Selection.End(xlUp).Select
* * Selection.End(xlUp).Select
* * ActiveCell.Offset(2, 0).Select
* * ActiveCell.FormulaR1C1 = "=SUM(R11C:R[-2]C)"

Dim rngStart As Range
Dim rngEnd As Range

Selection.End(xlDown).Select
Set rngEnd = ActiveCell
Selection.End(xlDown).Select
Set rngStart = ActiveCell
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = Application.Sum(Range(rngStart, rngEnd))

This line will do the sum:
ActiveCell.FormulaR1C1 = Application.Sum(Range(rngStart, rngEnd))

Basically, the last line in the code will do the sum, and the value is hard
coded in the cell. *I'd really like to see the =sum() function in the cell. *

I tried this, but couldn't get it to work:
ActiveCell.FormulaR1C1 = "=SUM(" & rngStart & ":" & rngEnd & ")"

Any ideas about what I'm doing wrong?

Thanks!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"Matthew Herbert" wrote:
On Aug 14, 11:06 am, ryguy7272
wrote:
If I record a macro, I can get below the range, and then hit Ctrl + Shift +
up arrow. *I get this code as a result:
* * ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-2]C)"

Excel knows to stop at the end of the used range, but the R[-13] is a
hard-code solution, so that won't work. *Is there a VBA equivalent to Ctrl +
Shift + Up?

Thanks,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..

"Matthew Herbert" wrote:
On Aug 14, 10:23 am, ryguy7272
wrote:
I’ve got a variable little groups of cells that need to be summed. *They can
appear higher or lower on a sheet and the only way I can think of identifying
the groups is as follows. *Look for an indicator in Column A, which is
actually ‘(A)’, then move right 5 cells and up 2 cells, but the group of
cells that need to be summed is variable.

Please look at my code and offer suggestions:

* * Cells.Find(What:="(A)*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
* * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
* * * * False, SearchFormat:=False).Activate
* * * * ActiveCell.Offset(0, 5).Select
* * * * ActiveCell.Offset(-2, 0).Select

* * * * RowCount = 12
* * * * ActiveCell.FormulaR1C1 = "=SUM(R[" & -RowCount & "]C:R[-2]C)"

The number of rows that I need to sum will not always be 12, it could be
anything. *In my current example, the array goes from F132:F143..

Any ideas on how to do this?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..

Ryan,

If there is no way to determine the row count then you will be out of
luck; however, if you have some other marker (similar to your "(A)",
for example) then it won't be much of a problem. *The computer simply
works on your behalf, so if you can observe a repeatable system for
determining the row count, then it can be coded.

Best,

Matthew Herbert- Hide quoted text -

- Show quoted text -

Ryan,

Yes, there is an equivalent to Ctrl+Shift+Arrow Key. *The equivalent
is the End method of the Range object. *For example, you can have Range
("A1").End(xlDown), wherein the xlDirection can be xlDown, xlUp,
xlLeft, or xlRight. *Also, maybe you like R1C1 notation, but I find it
less intuative than A1 notation. *Though the macro recorder records in
R1C1 notation, you don't have to code in R1C1.

I did some guess work below on where you want the formula to actually
reside, so the message boxes will let you see how the macro is
behaving. *Adjust the code as you will and feel free to take out the
message boxes. *(If you don't like the MsgBox popup then use
Debug.Print in place of MsgBox. *Debug.Print will print to the
Immediate Window -- View | Immediate Window). *Also, be sure to step
through your code line by line via Debug | Step Into (F8) -- simply
hit F8 multiple times.

Best,

Matt

Dim rngStart As Range
Dim rngEnd As Range

Set rngStart = Cells.Find(What:="(A)*", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
* * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
* * * * False, SearchFormat:=False).Offset(-2, 5)

MsgBox "Starting range: " & rngStart.Address

Set rngEnd = rngStart.End(xlUp)

MsgBox "Ending range: " & rngEnd.Address

MsgBox "Formula range: " & rngEnd.Offset(-1, 0).Address(False, False)
& vbLf & _
* * * *"Sum range * *: " & Range(rngStart, rngEnd).Address(False,
False)

rngEnd.Offset(-1, 0).Formula = "=SUM(" & Range(rngStart,
rngEnd).Address(False, False) & ")"- Hide quoted text -

- Show quoted text -

Ryan,

It probably isn't working because you are using the FormulaR1C1
property with an A1 notation address. FormulaR1C1 expects an address
in R1C1 notation. The address property of a range has a
ReferenceStyle parameter that will allow you to specify R1C1 or A1
notation; A1 notation is the default setting. So, your code has a
R1C1 property but the rngStart and rngEnd are in the default A1
notation. I'm anticipating that you could fix you code with the
following:

ActiveCell.FormulaR1C1 = "=SUM(" & Range(rngStart, rngEnd).Address
(ReferenceStyle:=xlR1C1) & ")"

Again, I don't use R1C1, so I haven't tested this, but it should
work. Also, I'm not sure why you are using .Select and .Activate.
These two methods slow things down, and, in general, you don't need
them. I typically use .Select/.Activate after the program is finished
running when I want the spreadsheet focus to be in a specific
spreadsheet location. Use the range objects by creating these objects
with the Set statment (as noted in my previous coding), and reference
your code from these objects. Also, if you need to string
multiple .End methods together, you can. For example, rngStart.End
(xlDown).End(xlToRight).End(xlUp) is completely okay. (I see that you
used three .End(xlUp) in a row. You have to be careful when doing
this because your data needs to be in a certain format for this to
work every time. If a blank row gets deleted and the data set is
contiguous again, this will likely fail. You may want to consider a
loop instead).

Best,

Matt

It mostly makes sense, and it definitely works. I'll have to study this
more. Thanks so much for all the help Matt. I learned something new today.

Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Matthew Herbert" wrote:

On Aug 16, 5:59 pm, ryguy7272
wrote:
Thanks for all the help Matt!!! I used your idea and got it working.

Code below:

Cells.Find(What:="(A)*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 5).Select

Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R11C:R[-2]C)"

Dim rngStart As Range
Dim rngEnd As Range

Selection.End(xlDown).Select
Set rngEnd = ActiveCell
Selection.End(xlDown).Select
Set rngStart = ActiveCell
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = Application.Sum(Range(rngStart, rngEnd))

This line will do the sum:
ActiveCell.FormulaR1C1 = Application.Sum(Range(rngStart, rngEnd))

Basically, the last line in the code will do the sum, and the value is hard
coded in the cell. I'd really like to see the =sum() function in the cell.

I tried this, but couldn't get it to work:
ActiveCell.FormulaR1C1 = "=SUM(" & rngStart & ":" & rngEnd & ")"

Any ideas about what I'm doing wrong?

Thanks!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"Matthew Herbert" wrote:
On Aug 14, 11:06 am, ryguy7272
wrote:
If I record a macro, I can get below the range, and then hit Ctrl + Shift +
up arrow. I get this code as a result:
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-2]C)"

Excel knows to stop at the end of the used range, but the R[-13] is a
hard-code solution, so that won't work. Is there a VBA equivalent to Ctrl +
Shift + Up?

Thanks,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..

"Matthew Herbert" wrote:
On Aug 14, 10:23 am, ryguy7272
wrote:
Ive got a variable little groups of cells that need to be summed. They can
appear higher or lower on a sheet and the only way I can think of identifying
the groups is as follows. Look for an indicator in Column A, which is
actually €˜(A), then move right 5 cells and up 2 cells, but the group of
cells that need to be summed is variable.

Please look at my code and offer suggestions:

Cells.Find(What:="(A)*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 5).Select
ActiveCell.Offset(-2, 0).Select

RowCount = 12
ActiveCell.FormulaR1C1 = "=SUM(R[" & -RowCount & "]C:R[-2]C)"

The number of rows that I need to sum will not always be 12, it could be
anything. In my current example, the array goes from F132:F143..

Any ideas on how to do this?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..

Ryan,

If there is no way to determine the row count then you will be out of
luck; however, if you have some other marker (similar to your "(A)",
for example) then it won't be much of a problem. The computer simply
works on your behalf, so if you can observe a repeatable system for
determining the row count, then it can be coded.

Best,

Matthew Herbert- Hide quoted text -

- Show quoted text -

Ryan,

Yes, there is an equivalent to Ctrl+Shift+Arrow Key. The equivalent
is the End method of the Range object. For example, you can have Range
("A1").End(xlDown), wherein the xlDirection can be xlDown, xlUp,
xlLeft, or xlRight. Also, maybe you like R1C1 notation, but I find it
less intuative than A1 notation. Though the macro recorder records in
R1C1 notation, you don't have to code in R1C1.

I did some guess work below on where you want the formula to actually
reside, so the message boxes will let you see how the macro is
behaving. Adjust the code as you will and feel free to take out the
message boxes. (If you don't like the MsgBox popup then use
Debug.Print in place of MsgBox. Debug.Print will print to the
Immediate Window -- View | Immediate Window). Also, be sure to step
through your code line by line via Debug | Step Into (F8) -- simply
hit F8 multiple times.

Best,

Matt

Dim rngStart As Range
Dim rngEnd As Range

Set rngStart = Cells.Find(What:="(A)*", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Offset(-2, 5)

MsgBox "Starting range: " & rngStart.Address

Set rngEnd = rngStart.End(xlUp)

MsgBox "Ending range: " & rngEnd.Address

MsgBox "Formula range: " & rngEnd.Offset(-1, 0).Address(False, False)
& vbLf & _
"Sum range : " & Range(rngStart, rngEnd).Address(False,
False)

rngEnd.Offset(-1, 0).Formula = "=SUM(" & Range(rngStart,
rngEnd).Address(False, False) & ")"- Hide quoted text -

- Show quoted text -

Ryan,

It probably isn't working because you are using the FormulaR1C1
property with an A1 notation address. FormulaR1C1 expects an address
in R1C1 notation. The address property of a range has a
ReferenceStyle parameter that will allow you to specify R1C1 or A1
notation; A1 notation is the default setting. So, your code has a
R1C1 property but the rngStart and rngEnd are in the default A1
notation. I'm anticipating that you could fix you code with the
following:

ActiveCell.FormulaR1C1 = "=SUM(" & Range(rngStart, rngEnd).Address
(ReferenceStyle:=xlR1C1) & ")"

Again, I don't use R1C1, so I haven't tested this, but it should
work. Also, I'm not sure why you are using .Select and .Activate.
These two methods slow things down, and, in general, you don't need
them. I typically use .Select/.Activate after the program is finished
running when I want the spreadsheet focus to be in a specific
spreadsheet location. Use the range objects by creating these objects
with the Set statment (as noted in my previous coding), and reference
your code from these objects. Also, if you need to string
multiple .End methods together, you can. For example, rngStart.End
(xlDown).End(xlToRight).End(xlUp) is completely okay. (I see that you
used three .End(xlUp) in a row. You have to be careful when doing
this because your data needs to be in a certain format for this to
work every time. If a blank row gets deleted and the data set is
contiguous again, this will likely fail. You may want to consider a
loop instead).

Best,

Matt