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
