Create a macro

Closed
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013
- Aug 14, 2013 at 12:29 PM
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013
- Aug 21, 2013 at 09:59 AM
Hello,

Please help. I am hoping that someone will be kind enough to adapt this macro that I can run each month that will return the following information. I have a list of credit card entries on an excel spreadsheet, and the R? column is used to note if a receipt has been submitted for that charge (X meaning a receipt was submitted). I need to do a countif in the R column for each cardholder to give me a total number of receipts submitted, then a total number of charges made in the T column (I know this is an =Rows function), and then a percentage of receipts turned in in the % column. I need the answers to remain as the function, as I continue to enter an x as receipts are turned in, which will continually change the R and %. As you can see, both the number and the name must be the same, as I often have the same number for 2 different people. I have no tech support and I am only capable of creating simple record macros. This type of macro is well beyond my scope. I was sent the attached macro, which totals the amount, and I thought I could adapt it to do the other functions, but it is well beyond my scope. Any help would be greatly appreciated.

# NAME AMOUNT R? R T %
1234 CHURCH, A $25.00 X
1234 CHURCH, A $26.00
1234 CHURCH, A $41.00 X
1234 CHURCH, A $57.00
1234 CHURCH, A $89.00 X
1234 CHURCH, A $45.00
1234 CHURCH, A $5.00 X
1234 CHURCH, A $7.00
1234 ME, L $89.00 X
1234 ME, L $91.00 X
1234 ME, L $64.00
1234 ME, L $28.00
1234 ME, L $3.00 X
1234 ME, L $8.00 X
1234 ME, L $4.00 X
5678 DOE, J $67.00
5678 DOE, J $15.00 X
5678 DOE, J $96.00 X
5678 SONG, R $45.00
5678 SONG, R $2.00 X
5678 SONG, R $7.00
5678 SONG, R $94.00 X
5678 SONG, R $45.00


Sub Totals()


Dim r1 As Range, c1 As Range, r As Range, filt As Range, filt1 As Range
Dim cfilt As Range, ssum As Double, j As Integer
Worksheets("sheet1").Activate

Set r1 = Range(Range("A1"), Range("A1").End(xlDown))
Set r = Range("A1").CurrentRegion
r.Sort Key1:=Range("B1"), Header:=xlYes
Set filt = Range("A1").End(xlDown).Offset(5, 0)
r1.AdvancedFilter xlFilterCopy, , filt, True
Set filt = Range(filt.Offset(1, 0), filt.End(xlDown))

For Each cfilt In filt
r.AutoFilter field:=1, Criteria1:=cfilt
ssum = WorksheetFunction.Sum(r.Columns("N:N").SpecialCells(xlCellTypeVisible))
j = Range("c1").End(xlDown).Row
Cells(j, "O") = ssum
ssum = 0
Columns("O:O").Select
Selection.Style = "Currency"


Next cfilt
ActiveSheet.AutoFilterMode = False
Range(Range("A1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete


End Sub

4 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 15, 2013 at 12:17 AM
some confusion in my mind. same number has two names. besides the dellimiters in the data sheet is not clear. upload your file to
speedyshare.com
and copy the address for downloading
before that try to give two examle results in the data sheeset and then upload.explain again if necessary
0
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013

Aug 16, 2013 at 08:37 AM
Sometimes two different people will have the same last 4 digits of a card number, and sometimes my cardholders have the same name (Joe Smith). That is why both # & Name must match before performing the needed functions. I have created a template thru SpeedyShare. The link is [URL=http://www.speedy-share.com/7hf0c1o0bmdp.html]CreateMacro.xlsx - 19.2 Kb[/URL]. I performed the functions in the three columns I would like to have a macro to auto perform, and shaded them in grey. I do so much appreciate your interest and your help. I have trouble understanding the language needed to create the macro. Thank you.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 17, 2013 at 01:55 AM
see T2 which is concatenate formula A2 and B2
T2 is copied down till data is there (row no. 76)
the result of the macro is U,V,W so that you can check your manual calculations in Q R S.

confirm this will do

the modified macro is called
lounnacTotals

donwload the file from web address

http://speedy.sh/ufMEp/lounnac-CreateMacro-130807.xlsm

open and enable macros.

the macro is module and also repeated here


Sub lounnacTotals()
Dim r1 As Range, c1 As Range, r As Range, filt As Range, filt1 As Range
Dim cfilt As Range, ssum As Double, j As Integer
Application.ScreenUpdating = False
Worksheets("sheet1").Activate

Range("U1:W1").EntireColumn.Delete

Set r1 = Range(Range("T1"), Range("T1").End(xlDown))
Set r = Range("A1").CurrentRegion
r.Sort Key1:=Range("B1"), Header:=xlYes
Set filt = Range("A1").End(xlDown).Offset(15, 0)
r1.AdvancedFilter xlFilterCopy, , filt, True
Set filt = Range(filt.Offset(1, 0), filt.End(xlDown))
For Each cfilt In filt
r.AutoFilter field:=Range("T1").Column, Criteria1:=cfilt
ssum = WorksheetFunction.Sum(r.Columns("N:N").SpecialCells(xlCellTypeVisible))
j = Range("c1").End(xlDown).Row
Cells(j, "O") = ssum
Cells(j, "U") = WorksheetFunction.CountA(Columns("M:M").SpecialCells(xlCellTypeVisible)) - 1
Cells(j, "V") = WorksheetFunction.CountA(Columns("B:B").SpecialCells(xlCellTypeVisible)) - 1
Cells(j, "W") = Cells(j, "U") / Cells(j, "V")

ActiveSheet.AutoFilterMode = False





'ssum = 0
'Columns("O:O").Select
'Selection.Style = "Currency"
Next cfilt
Columns("O:O").Cells.NumberFormat = "$#,##0.00"
Columns("W:W").Cells.NumberFormat = "0.00%"
Range("U1:W1").EntireColumn.AutoFit
Range(Range("A1").End(xlDown).Offset(15, 0), Cells(Rows.Count, "A")).EntireRow.Delete
Application.ScreenUpdating = True
MsgBox "macro over"
End Sub
0
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013

Aug 19, 2013 at 03:32 PM
Thank you for all your hard work. We are so close to what I need; however, I wanted the macro to do the manual calculations I did in Columns Q, R & S, and leave the result as the formula. If column Q remains as a 'countif' function, the M column can change as I add "x' to denote a receipt turned in, and Q will reflect those changes and return a different percentage in S (which has the function of Q/R) as it changes. The purpose is to do a continual count of the number of receipts submitted in column M.. Does that make sense? I adapted your macro to make the computations in the Q, R, S columns, but it is returning the value, and I need it to remain as the functions (Q as a countif function, R as =rows function, and S as percentage (Q/R)). I so truly appreciate all your help.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 19, 2013 at 11:53 PM
does it mean you DO NOT WANT A MACRO but only formulas so tht automaticalll reslts will change????
0
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013

Aug 20, 2013 at 02:39 PM
I want the macro itself to auto-perform the functions for each cardholder, and leave the results as the function, not the value. Are you saying a macro cannot be created to perform those functions? That was my fear. I have over 300 cardholders with charges each month, and to go down and manually sum each one, then do the countif function, rows function, and percentage calculation for each one is very time consuming. Your macro gave me the results I wanted, but did not allow for changes as additional 'x's were placed in the receipt column.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 20, 2013 at 09:41 PM
every montgh you can run the macro
whenever ou run the macro the columns U to W are deleted and even if some more rows of data are added I am sure that the macro will take care off. you need not do anything manuall except to click "srun" the macro.
try this:

1.run the macro
2. now add some ficitious data at two or three rows after the end of current rowa. .;
3. now run te macro
4. see whether you get what ou want
5. still if there is probolem revert back to newsgsroup explaining clarly what the problem, is.
0
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013

Aug 21, 2013 at 09:59 AM
Thank you. Sorry I couldn't explain clearly enough what I needed. Your efforts were appreciated.
0