Looping through Cells

[Gary Paris]

I have an expense sheet. The data includes: Item Description, Date, Amount,
Employee

I would like to calculate totals for each employee separately.

This is how the data looks:

Widget 1, 12/23/04, 43.45, Joe
Widget 2, 12,24/04, 21.55, Edward
Widget 3, 12/01/04, 15.55, Joe

How can I do this automatically (each time I make a change).

Sometimes there could be 10 entries, 50 entries, 20 entries, etc... I would
like a way to figure out the totals when the sheet is open or becomes
active. It seems using the SUMIF command, I would have to plug in the
ranges.

I would like to loop through the records and when there is no entry in the
"employee" field, I'm done.

 

[Jim May]

I'd take a look at (and consider) Data, Filter, AutoFilter
It's pretty versatile. Once assigned, just click (on the Down Arrow) of
Column header Employee.
Plus I often Insert 3 blank rows at the top of the screen, with my
datacolumnsdescriptions
in say Row 4, data starting in row5 ... 1000; but back to row, say in C2
enter:
=subtotal(9,C5:C1000)... Lastly, I go to cell A5 and do (at the menu)
Windows Freeze Panes.
HTH
Jim May

 

[Gary Paris]

I looked at the AutoFilter command, and was able to display the data for
each employee.

On my sheet for example, the dollar amounts are in C2 thru C36, but the
Grand Total for each employee is in C40. How can I programatically get the
total for the employee in question?

Why can't I loop through the data examining Row "D" for the persons name? I
know this is more work, but I would like to learn more.

Can you or anyone provide some code so I can loop through the data, looking
for a person's name and then generating a total amount? This is what I
would like. Please help.

Thanks,

Gary

 

[Jim May]

In a standard Module put:

Sub SumByEmployee()
MyPick = InputBox("What Employee Do you Want?")
For Each c In Range("D236")
If c = MyPick Then mTotal = mTotal + c.Offset(, -1)
Next c
Range("C40") = mTotal
End Sub

HTH

 

[Gary Paris]

Thanks Jim for your code,

It didn't run the way you coded it here because I have 3 sheets in the book.
I added
Sheets("Expenses").Select

Then after the For Each c in Range, I added
Range(c.address).Select

I hope this is correct.

The problem is that if I make a change anywhere else on any sheet, my
routine runs. This is because I call it from the Workbook_SheetChange
routine.

Is there any way of running my routine and placing the cursor back to the
Sheet and Cell I started from?

Thanks,

Gary

 

[Myrna Larson]

The Sh and Target arguments of the SheetChange event code tell you the
worksheet and cell which was changed and which triggered the event. Maybe code
like this will do the trick. I disabled event processing on the assumption
that your SumByEmployee code writes to a worksheet. If that isn't true, you
can eliminate the lines that turn it off and back on.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
SumByEmployee
Sh.Activate
Target.Cells(1).Activate
Application.EnableEvents = True
End Sub