Dahlia
Member
Hye,
I have been trying to embed this ranking formulas (inspired by Chandoo's contribution on Top X Chart) into pivot table with the intention to let it calculate automatically when the range and data changes. Unfortunately, I really couldn't except the "Dummy" column. So, I converted the formulas as macros. They work successfully, just that consumes inconsiderable amount of time to process even just for 20 to 30 rows of data.
Appreciate is anyone who is expert to correct or improve my coding below to allow much faster processing :-
I just realized that the Dummy auto calculation I did in pivot will not give correct calculation when I select "All" for parameter months.
I guess that is another problem I need to ask the expertise to help. Because I wish to do a validation list for the month names, which is when I select, all the pivots will be filtered automatically and all the charts by all types of data group will be populated automatically. This way I can efficiently complete my stats report either weekly, monthly, quarterly or all.
Attached is the sample file. Appreciate any help to make it better.
Post Moved By Mod.
.
I have been trying to embed this ranking formulas (inspired by Chandoo's contribution on Top X Chart) into pivot table with the intention to let it calculate automatically when the range and data changes. Unfortunately, I really couldn't except the "Dummy" column. So, I converted the formulas as macros. They work successfully, just that consumes inconsiderable amount of time to process even just for 20 to 30 rows of data.
Appreciate is anyone who is expert to correct or improve my coding below to allow much faster processing :-
Code:
Sub Rank()
Dim FPart1 As String
Dim FPart2 As String
Dim FPart3 As String
Dim LRow As Integer
Dim SRow As Integer
Application.ScreenUpdating = False
'LRow = Range("B1", Range("B1").End(xlDown)).Rows.Count
'With ActiveSheet
'LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
'End With
OldRow = Range("E" & Rows.Count).End(xlUp).Row
'MsgBox OldRow
LRow = Range("B" & Rows.Count).End(xlUp).Row
SRow = Range("A2").Value
'MsgBox LRow
ActiveSheet.Range("E" & SRow & ":E" & OldRow).ClearContents
FPart1 = "=IF(XXXXX<=R2C6,INDEX(INDIRECT(""$B$""&R2C1&"":$B$""&LOOKUP(2,1/(C[-3]""""),ROW(C[-3]))),MATCH(LARGE(YYYYY,ROWS(INDIRECT(CHAR(COLUMN()+64)&""$""&R2C1&"":""&CHAR(COLUMN()+64)&ROW()))),YYYYY,0)),IF(XXXXX<=R2C6+1,""All Other"",""""))"
FPart2 = "ROWS(INDIRECT(CHAR(COLUMN()+64)&""$""&R2C1&"":""&CHAR(COLUMN()+64)&ROW()))"
FPart3 = "INDIRECT(""$D$""&R2C1&"":$D$""&LOOKUP(2,1/(C[-3]""""),ROW(C[-3])))"
Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("E" & SRow)
.Formula = FPart1
.Replace "XXXXX", FPart2, lookat:=xlPart
.Replace "YYYYY", FPart3, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1
Range("E" & SRow).Select
Selection.AutoFill Destination:=Range("E" & SRow & ":E" & LRow)
Calculate
Range("E" & SRow & ":E" & LRow).Select
Range("E" & SRow).Select
Application.ScreenUpdating = True
End SubI just realized that the Dummy auto calculation I did in pivot will not give correct calculation when I select "All" for parameter months.
I guess that is another problem I need to ask the expertise to help. Because I wish to do a validation list for the month names, which is when I select, all the pivots will be filtered automatically and all the charts by all types of data group will be populated automatically. This way I can efficiently complete my stats report either weekly, monthly, quarterly or all.Attached is the sample file. Appreciate any help to make it better.
Post Moved By Mod.
.
