Determining range
 

You might try using the built in Autosubtotals in Excel. It keys off of a
change in the description. Sure beats having to write code.


"Sam Carleton" wrote in message
...
I have been programming for a few years, about 8, now in other
languages, like C++. This is my first endeavor with Excel and need
some direction.

My wife has a business that she is just starting up which is one of
those home show business. I created a quick spreadsheet that allows
her to enter her numbers for a show on one line and it would show
the total profit in the last column (I#). I am them summing up that
whole colume for a running total of profit. The first colume has the
date (A#).

What I would like to do is create a VBA sub that will go row-by-row
and create monthly totals which would be placed in J# where # is the
last row for a given month. I can pseudo code it, but I cannot
figure out how to actuallly code it. Can someone help me out? Is
there a better approach?

pseudo code:

Sub SubTotalMonths()
var lastRow
var currentRow
var currentMonth
var monthTotal

currentRow = first row on active sheet
lastRow = currentRow
currentMonth = get month from cell A of currentRow
monthTotal = 0

while cell A of row is not nothing
if currentMonth < get month from cell A of currentRow Then
set cell J of lastRow = monthTotal
set currentMonth = get month from Cell A of currentRow
monthTotal = 0
End If

monthTotal = monthTotal + value of Cell I of currentRow
set lastRow = currentRow
set currentRow = next row down in the sheet
wend

set cell J of lastRow = monthTotal
End Sub

Another part of my goal is to have a spread sheet that my wife can
simply enter the number and not have to worry about formating each
cell, nor copying down the formula's from the row above. How would
I go about doing that? The basic idea is that there will be data i
rows 3 through 23, my wife will enter a new row in 24, then click on
one button to update the monthly totals and to do the formating and
calculations of the new row. I do not want her to have to havve
anything selecting of cells, or anything.

Is there a better way to do this?

Sam

Determining range
 

I have been programming for a few years, about 8, now in other
languages, like C++. This is my first endeavor with Excel and need
some direction.

My wife has a business that she is just starting up which is one of
those home show business. I created a quick spreadsheet that allows
her to enter her numbers for a show on one line and it would show
the total profit in the last column (I#). I am them summing up that
whole colume for a running total of profit. The first colume has the
date (A#).

What I would like to do is create a VBA sub that will go row-by-row
and create monthly totals which would be placed in J# where # is the
last row for a given month. I can pseudo code it, but I cannot
figure out how to actuallly code it. Can someone help me out? Is
there a better approach?

pseudo code:

Sub SubTotalMonths()
var lastRow
var currentRow
var currentMonth
var monthTotal

currentRow = first row on active sheet
lastRow = currentRow
currentMonth = get month from cell A of currentRow
monthTotal = 0

while cell A of row is not nothing
if currentMonth < get month from cell A of currentRow Then
set cell J of lastRow = monthTotal
set currentMonth = get month from Cell A of currentRow
monthTotal = 0
End If

monthTotal = monthTotal + value of Cell I of currentRow
set lastRow = currentRow
set currentRow = next row down in the sheet
wend

set cell J of lastRow = monthTotal
End Sub

Another part of my goal is to have a spread sheet that my wife can
simply enter the number and not have to worry about formating each
cell, nor copying down the formula's from the row above. How would
I go about doing that? The basic idea is that there will be data i
rows 3 through 23, my wife will enter a new row in 24, then click on
one button to update the monthly totals and to do the formating and
calculations of the new row. I do not want her to have to havve
anything selecting of cells, or anything.

Is there a better way to do this?

Sam

Determining range
 

On Wed, 29 Oct 2003 at 12:14 GMT, Mike Macgowan wrote:
You might try using the built in Autosubtotals in Excel. It keys off of a
change in the description. Sure beats having to write code.

I agree, using a function is always prefered over writing code.

Another part of my goal is to have a spread sheet that my wife
can simply enter the number and not have to worry about formating
each cell, nor copying down the formula's from the row above.
How would I go about doing that? The basic idea is that there
will be data i rows 3 through 23, my wife will enter a new row in
24, then click on one button to update the monthly totals and to
do the formating and calculations of the new row. I do not want
her to have to havve anything selecting of cells, or anything.

Now what about this part? How do I copy the formating and the
formula's down one level each time?

Sam

Determining range
 

At least with XL2002, if you insert a row with Insert | Rows, XL copies
the formatting from the previous row.

You will be surprised at how much is natively possible with XL; and,
when one must use code, at how powerful the XL object model is.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , scarleton-
says...
On Wed, 29 Oct 2003 at 12:14 GMT, Mike Macgowan wrote:
You might try using the built in Autosubtotals in Excel. It keys off of a
change in the description. Sure beats having to write code.

I agree, using a function is always prefered over writing code.

Another part of my goal is to have a spread sheet that my wife
can simply enter the number and not have to worry about formating
each cell, nor copying down the formula's from the row above.
How would I go about doing that? The basic idea is that there
will be data i rows 3 through 23, my wife will enter a new row in
24, then click on one button to update the monthly totals and to
do the formating and calculations of the new row. I do not want
her to have to havve anything selecting of cells, or anything.

Now what about this part? How do I copy the formating and the
formula's down one level each time?

Sam