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) & ")"