Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
I need to be able to count how many clients are repeated in a record of
attendance sheet. Clients are identified with a unique client number. I would like to run a macro that searched the record sheet for clients with multiple entries and copies their details (which are organised across a row) onto another sheet within the work book. Would also like to total the amount of money they received. Date Client Number Voucher# Amount Provided 30/03/2009 66 O0685 $ 65.00 12/12/2008 67 O0392 $ 75.00 9/06/2009 67 P 0021 $ 150.00 22/06/2009 68 P 0686 $ 100.00 In the above example client # 67 is entered twice so I need the macro to copy that into another worksheet so it looks like this Client# Number Total of visits Received 67 2 $225.00 Is this possible? No Pivot Tables Please |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
I did the macro the way I thought would run the fastest. the method I used
is a combination of formulas and VBA. to get the unique clients with more the one entry I used this formula in sheet 1 and copyied down an unused column IV "=COUNTIF(B$2:B2,B2)" entries with a rusult of 2 are the clients with more than one entry. I then used autofilter and filtered the result of 2. I then used the specialcells method to copy the visible cells in column b which are the unique duplicate clients and put these number in sheet 2. Then used a countif formula in column 2 to get the counts of entries for each client and used sumif to get the dollar amounts. Sub getduplicates() With Sheets("Sheet1") LastRow = .Range("A" & Rows.Count).End(xlUp).Row 'get count of number of clients from row two to row 'where formula is placed. 'the rows with 2 are the unique client with duplices 'put formula in usused column IV .Range("IV2").Formula = _ "=COUNTIF(B$2:B2,B2)" 'copy formula down column IV .Range("IV2").Copy _ Destination:=.Range("IV2:IV" & LastRow) 'put a header in IV1 to make autofilter work properly .Range("IV1") = "Count" 'autofilter column IV for 2 Range("IV1").Select .Range("IV1").AutoFilter _ Field:=1, _ Criteria1:="2" 'copy visible client number from column B .Range("B2:B" & LastRow) _ .SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("sheet2").Range("A2") 'remove autofilter from sheet 1 .Range("IV1").AutoFilter 'remove formulas from column IV .Columns("IV").Delete End With With Sheets("Sheet2") 'add header row to sheet .Range("A1") = "Client#" .Range("B1") = "Number of vistis" .Range("C1") = "Total Received" 'add formula for number of visits in column B .Range("B2").Formula = _ "=COUNTIF(Sheet1!B2:B" & LastRow & ",A2)" 'add formula for total Received in column C .Range("C2").Formula = _ "=SumIF(Sheet1!B2:B" & LastRow & ",A2," & _ "Sheet1!D2:D" & LastRow & ")" LastRow = .Range("A" & Rows.Count).End(xlUp).Row 'copy formulas down the column .Range("B2:C2").Copy _ Destination:=.Range("B2:C" & LastRow) .Activate .Range("A1").Select .Columns("A:C").Columns.AutoFit End With End Sub "Ben" wrote: I need to be able to count how many clients are repeated in a record of attendance sheet. Clients are identified with a unique client number. I would like to run a macro that searched the record sheet for clients with multiple entries and copies their details (which are organised across a row) onto another sheet within the work book. Would also like to total the amount of money they received. Date Client Number Voucher# Amount Provided 30/03/2009 66 O0685 $ 65.00 12/12/2008 67 O0392 $ 75.00 9/06/2009 67 P 0021 $ 150.00 22/06/2009 68 P 0686 $ 100.00 In the above example client # 67 is entered twice so I need the macro to copy that into another worksheet so it looks like this Client# Number Total of visits Received 67 2 $225.00 Is this possible? No Pivot Tables Please |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
Hi Joel
The macro does not work. I get a 400 error |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
This is the message for a Err 400:
You can't use the Show method to display a visible form as modal. This error has the following cause and solution: a.. You tried to use Show, with the style argument set to 1 - vbModal, on an already visible form. Use either the Unload statement or the Hide method on the form before trying to show it as a modal form. So, since there is no form involved in the code Joel provided, the question arises, is there something you forgot to include in your original post? "Ben" wrote in message ... Hi Joel The macro does not work. I get a 400 error |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
The only thing I forgot to say was that the sheet with all the data is call
"Voucher Records". Would that make a difference? "JLGWhiz" wrote: This is the message for a Err 400: You can't use the Show method to display a visible form as modal. This error has the following cause and solution: a.. You tried to use Show, with the style argument set to 1 - vbModal, on an already visible form. Use either the Unload statement or the Hide method on the form before trying to show it as a modal form. So, since there is no form involved in the code Joel provided, the question arises, is there something you forgot to include in your original post? "Ben" wrote in message ... Hi Joel The macro does not work. I get a 400 error |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
I don't know why you would get a VBA error 400, which is telling you that
you are trying to open a form that is already open. There is nothing in the code that Joel provided that calls a form to Load or Show. If you click the Debug button when the error message appears, which line of code is highlighted? "Ben" wrote in message ... The only thing I forgot to say was that the sheet with all the data is call "Voucher Records". Would that make a difference? "JLGWhiz" wrote: This is the message for a Err 400: You can't use the Show method to display a visible form as modal. This error has the following cause and solution: a.. You tried to use Show, with the style argument set to 1 - vbModal, on an already visible form. Use either the Unload statement or the Hide method on the form before trying to show it as a modal form. So, since there is no form involved in the code Joel provided, the question arises, is there something you forgot to include in your original post? "Ben" wrote in message ... Hi Joel The macro does not work. I get a 400 error |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
macro needed | Excel Discussion (Misc queries) | |||
Macro needed to set macro security in Excel to minimum | Excel Programming | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |