Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Cells
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Cells
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" wrote in message news:f2Zyd.9965$jn.4327@lakeread06... 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Cells
In a standard Module put:
Sub SumByEmployee() MyPick = InputBox("What Employee Do you Want?") For Each c In Range("D2:D36") If c = MyPick Then mTotal = mTotal + c.Offset(, -1) Next c Range("C40") = mTotal End Sub HTH "Gary Paris" wrote in message ... 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" wrote in message news:f2Zyd.9965$jn.4327@lakeread06... 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Cells - Different Sheet
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 "Jim May" wrote in message news:Ap1zd.9988$jn.1749@lakeread06... In a standard Module put: Sub SumByEmployee() MyPick = InputBox("What Employee Do you Want?") For Each c In Range("D2:D36") If c = MyPick Then mTotal = mTotal + c.Offset(, -1) Next c Range("C40") = mTotal End Sub HTH "Gary Paris" wrote in message ... 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" wrote in message news:f2Zyd.9965$jn.4327@lakeread06... 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Cells - Different Sheet
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 On Fri, 24 Dec 2004 16:18:08 -0800, "Gary Paris" wrote: 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 "Jim May" wrote in message news:Ap1zd.9988$jn.1749@lakeread06... In a standard Module put: Sub SumByEmployee() MyPick = InputBox("What Employee Do you Want?") For Each c In Range("D2:D36") If c = MyPick Then mTotal = mTotal + c.Offset(, -1) Next c Range("C40") = mTotal End Sub HTH "Gary Paris" wrote in message ... 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" wrote in message news:f2Zyd.9965$jn.4327@lakeread06... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference cells when looping | Excel Discussion (Misc queries) | |||
Looping thru a range of cells | New Users to Excel | |||
looping through an giving values to cells in vba | Excel Discussion (Misc queries) | |||
Looping through (active??) cells problem | Excel Programming | |||
Looping through a range of cells | Excel Programming |