I'm new to "advanced" VBA macros and I'm having trouble. In general I
am trying to move through a spreadsheet, by column, making subtotals.
When my first formula is created, I move to the next line in the column
where I want the next formula. I am able to move to the areas I need
to, but I am having trouble writing the code to Sum the column up to
the last formula. The number of rows varies between formulas, so I am
trying to figure out how to tell excel to go to the previous formula,
move down one row and set that cell as the beginning range of the SUM
and then move to the bottom row above where the formula is and set that
active cell as the end of SUM range.

Any help is appreciated. Thanks.

Jim


---
Message posted from http://www.ExcelForum.com/

Jim,

Once you have a SUM formula, move across 1 column (down 1 row?) and save the
current column/row with code like

iFirst = ACtivecell.Column (.Row)

move on across, down, and when you know to SUM, do likewise into iLast,
taking 1 off, and the SUM is a simple

Activecell.FormulaR1C1 = "=SUM(RC" & IFirst & ":RC" & iLast & ")" or

Activecell.FormulaR1C1 = "=SUM(R" & IFirst & "C:R" & iLast & "C)"

if you really mean rows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"NewMacro " wrote in message
...
I'm new to "advanced" VBA macros and I'm having trouble. In general I
am trying to move through a spreadsheet, by column, making subtotals.
When my first formula is created, I move to the next line in the column
where I want the next formula. I am able to move to the areas I need
to, but I am having trouble writing the code to Sum the column up to
the last formula. The number of rows varies between formulas, so I am
trying to figure out how to tell excel to go to the previous formula,
move down one row and set that cell as the beginning range of the SUM
and then move to the bottom row above where the formula is and set that
active cell as the end of SUM range.

Any help is appreciated. Thanks.

Jim


---
Message posted from http://www.ExcelForum.com/

Thanks Bob. I edited my macro and I think I'm almost there. This is
what I have:

iFirst = ActiveCell.Column
ActiveCell.FormulaR1C1 = "=SUM(R" & iFirst & "C:R" & iLast & "C)"
ActiveCell.Offset(1, -9).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.FormulaR1C1 = "=SUM(R" & iFirst & "C:R" & iLast & "C)"
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

The resulting formula in Cell K10 is: =SUM(K$10:K10)
K16 is =SUM(K$16:K16) and
so on.
It should be Cell K10 =SUM(K4:K9)
K16 is =SUM(K11:K15) and
so on
I'm not sure if I'm starting in the right cell or not. The formulas
are getting to the right places, but I'm off on the formula itself.
What am I missing?


---
Message posted from http://www.ExcelForum.com/

Some of the code seems to be missing. Where is the Do, and where is iLast
set? Show us those bits and I am sure we will sort it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"NewMacro " wrote in message
...
Thanks Bob. I edited my macro and I think I'm almost there. This is
what I have:

iFirst = ActiveCell.Column
ActiveCell.FormulaR1C1 = "=SUM(R" & iFirst & "C:R" & iLast & "C)"
ActiveCell.Offset(1, -9).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.FormulaR1C1 = "=SUM(R" & iFirst & "C:R" & iLast & "C)"
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

The resulting formula in Cell K10 is: =SUM(K$10:K10)
K16 is =SUM(K$16:K16) and
so on.
It should be Cell K10 =SUM(K4:K9)
K16 is =SUM(K11:K15) and
so on
I'm not sure if I'm starting in the right cell or not. The formulas
are getting to the right places, but I'm off on the formula itself.
What am I missing?


---
Message posted from http://www.ExcelForum.com/

Let's try this again.

Do
iFirst = ActiveCell.Column
ActiveCell.FormulaR1C1 = "=SUM(RC" & iFirst & ":RC" & iLast & ")"
ActiveCell.Offset(1, -9).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.FormulaR1C1 = "=SUM(RC" & iFirst & ":RC" & iLast & ")"
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

That's the correct code that gives me the previous results.
I guess I didn't know I had to set the iLast. How is it done? We ar
fast approaching my knowledge base limit!! :(

Thanks for your help

--
Message posted from http://www.ExcelForum.com

I'm really struggling with this as I have no idea what your data looks like,
and so I am struggling to understand what needs to be done.

Why don't you mail your workbook direct to me at
bob . phillips @ tiscali . co . uk (without the spaces)
and I will try and sort it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"NewMacro " wrote in message
...
Let's try this again.

Do
iFirst = ActiveCell.Column
ActiveCell.FormulaR1C1 = "=SUM(RC" & iFirst & ":RC" & iLast & ")"
ActiveCell.Offset(1, -9).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.FormulaR1C1 = "=SUM(RC" & iFirst & ":RC" & iLast & ")"
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

That's the correct code that gives me the previous results.
I guess I didn't know I had to set the iLast. How is it done? We are
fast approaching my knowledge base limit!! :(

Thanks for your help.


---
Message posted from http://www.ExcelForum.com/