Any way I can speed this up? -- Changing formulas and such

M

Myriad_Rocker

Board Regular
Joined
Dec 1, 2004
Messages
67
I have the following code executing on a report that does various things depending on what is selected. What could I do to tweak this code to make it run quicker? It takes about 30 seconds to run as it is.

The code that calls this sub is setting calculation to manual, events to false, and screen updating to false.

Code: 
Sub ChangeYTDFormulas()
    Dim RowNum As Long
    'The row we're starting on
    RowNum = 16
    'Making sure we're on the right sheet
    Sheets("Weekly Sales Report").Select
    'Get the number of rows to do
    Do Until IsEmpty(Range("H" & RowNum).Value)
        RowNum = RowNum + 1
    Loop
    RowNum = RowNum - 1 'because we found the blank row, now we need the last row that had something!
    
    Select Case ActiveSheet.btnHighVolumeAmount.Value
        Case True
            ActiveSheet.Range("Y15") = "High Volume Wkly POS"
            ActiveSheet.Range("Z15") = "High Volume Wk Ending"
        
            Range("Y16").Select
            ActiveCell.Formula = "=IF(ISBLANK(S16),"""",MAX(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16))"
            Range("Z16").Select
            ActiveCell.Formula = "=IF(Y16="""","""",IF(Y16=0,"""",AA16))"
            Range("AA16").Select
            ActiveCell.Formula = "=TEXT(VLOOKUP((RIGHT(INDEX((INDEX($AH$15:$CG$15,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):$CG$15),0," & _
                                 "(MATCH(Y16,(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16),0))),5)),WeekConversionYTW,3,FALSE),""mm/dd/yy"")"
            Range("Y16:AA16").Select
            Selection.AutoFill Destination:=Range("Y16:AA" & RowNum), Type:=xlFillValues
    End Select
    
    Select Case ActiveSheet.btnRankwithinStore.Value
        Case True
            ActiveSheet.Range("Y15") = "Sales Rank"
            ActiveSheet.Range("Z15") = "Sales Rank within Region"
            
            Range("Y16").Select
            ActiveCell.Formula = "=IF(R16="""","""",VLOOKUP(R16,$CU$16:$EI$151,38,FALSE))"
            Range("Z16").Select
            ActiveCell.Formula = "=IF(R16="""","""",VLOOKUP(R16,$CU$16:$EI$151,39,FALSE))"

            Range("Y16:AA16").Select
            Selection.AutoFill Destination:=Range("Y16:AA" & RowNum), Type:=xlFillValues
    End Select
    
    'Changing formulas for regional #'s
    Dim RegionValue As String
    RegionValue = ActiveSheet.combo_Region.Value
    Select Case ActiveSheet.btnPOSSales.Value
        Case True
            If ActiveSheet.combo_Region.Value = "(All)" Then
                Range("V16").Formula = "=VLOOKUP(""Total"",SalesDataAggregate_Total,96,FALSE)" 'This is the grand total line
                Range("V17").Formula = "=IF(ISERR(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,101,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,100,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,99,FALSE)))),0," & _
                                       "IF(ISNA(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,101,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,100,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,99,FALSE)))),0," & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,101,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,100,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,99,FALSE)))))"
                Range("X16").Formula = "=V16/VLOOKUP(""Total"",SalesDataAggregate_Total,97,FALSE)-1" 'This is the grand total line
                Range("X17").Formula = "=IF(ISERR(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,102,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,101,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,100,FALSE)))-1),0," & _
                                       "IF(ISNA(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,102,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,101,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,100,FALSE)))-1),0,V17/" & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,102,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,101,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,100,FALSE)))-1))"
            Else
                Range("V16").Formula = "=VLOOKUP(""*""," & RegionValue & "Agg_Brand,91,FALSE)" 'This is the grand total line
                Range("V17").Formula = "=IF(ISERR(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,95,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,94,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,93,FALSE)))),0," & _
                                       "IF(ISNA(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,95,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,94,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,93,FALSE)))),0," & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,95,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,94,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,93,FALSE)))))"
                Range("X16").Formula = "=V16/VLOOKUP(""*""," & RegionValue & "Agg_Brand,92,FALSE)-1" 'This is the grand total line
                Range("X17").Formula = "=IF(ISERR(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,96,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,95,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,94,FALSE)))-1),0," & _
                                       "IF(ISNA(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,96,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,95,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,94,FALSE)))-1),0,V17/" & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,96,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,95,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,94,FALSE)))-1))"
            End If
            ActiveSheet.Range("V15") = "Rgnl POS $ - YTD"
            ActiveSheet.Range("W15") = "Rgnl POS $ Indx - YTD"
            ActiveSheet.Range("X15") = "Rgnl Growth vs YAGO - YTD"
            Range("V17").Select
            Selection.AutoFill Destination:=Range("V17:V" & RowNum)
            Range("X17").Select
            Selection.AutoFill Destination:=Range("X17:X" & RowNum)
    End Select
    
    Select Case ActiveSheet.btnPOSUnits.Value
        Case True
            If ActiveSheet.combo_Region.Value = "(All)" Then
                Range("V16").Formula = "=VLOOKUP(""Total"",SalesDataAggregate_Total,166,FALSE)" 'This is the grand total line
                Range("V17").Formula = "=IF(ISERR(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,171,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,170,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,169,FALSE)))),0," & _
                                       "IF(ISNA(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,171,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,170,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,169,FALSE)))),0," & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,171,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,170,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,169,FALSE)))))"
                Range("X16").Formula = "=V16/VLOOKUP(""Total"",SalesDataAggregate_Total,167,FALSE)-1" 'This is the grand total line
                Range("X17").Formula = "=IF(ISERR(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,172,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,171,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,170,FALSE)))-1),0," & _
                                       "IF(ISNA(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,172,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,171,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,170,FALSE)))-1),0,V17/" & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17),SalesDataAggregate_Desc,172,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17),SalesDataAggregate_SubPack,171,FALSE),VLOOKUP(B17,SalesDataAggregate_Brand,170,FALSE)))-1))"
            Else
                Range("V16").Formula = "=VLOOKUP(""*""," & RegionValue & "Agg_Brand,161,FALSE)" 'This is the grand total line
                Range("V17").Formula = "=IF(ISERR(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,165,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,164,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,163,FALSE)))),0," & _
                                       "IF(ISNA(IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,165,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,164,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,163,FALSE)))),0," & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,165,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,164,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,163,FALSE)))))"
                Range("X16").Formula = "=V16/VLOOKUP(""*""," & RegionValue & "Agg_Brand,162,FALSE)-1" 'This is the grand total line
                Range("X17").Formula = "=IF(ISERR(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,166,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,165,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,164,FALSE)))-1),0," & _
                                       "IF(ISNA(V17/IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,166,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,165,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,164,FALSE)))-1),0,V17/" & _
                                       "IF(LEFT(B17,6)=""      "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_Desc,166,FALSE)," & _
                                       "IF(LEFT(B17,4)=""    "",VLOOKUP(TRIM(B17)," & RegionValue & "Agg_SubPack,165,FALSE),VLOOKUP(B17," & RegionValue & "Agg_Brand,164,FALSE)))-1))"
            End If
            ActiveSheet.Range("V15") = "Rgnl POS Qty - YTD"
            ActiveSheet.Range("W15") = "Rgnl POS Qty Indx - YTD"
            ActiveSheet.Range("X15") = "Rgnl Growth vs YAGO - YTD"
            Range("V17").Select
            Selection.AutoFill Destination:=Range("V17:V" & RowNum)
            Range("X17").Select
            Selection.AutoFill Destination:=Range("X17:X" & RowNum)
    End Select
    Range("B15").Select
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hello

Start by removing the .Select's. For instance:

Code: 
Range("Y16").Select
        ActiveCell.Formula = "=IF(ISBLANK(S16),"""",MAX(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16))"

should be:

Code: 
Range("Y16").Formula = "=IF(ISBLANK(S16),"""",MAX(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16))"

Why not storing this formula in a (hidden) row or column, then just copying the cells if and when needed (instead of hardcoding a lot of things in the VBA code.

Wigi
 
Upvote 0
wigi said:
Hello

Start by removing the .Select's. For instance:

Code: 
Range("Y16").Select
        ActiveCell.Formula = "=IF(ISBLANK(S16),"""",MAX(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16))"

should be:

Code: 
Range("Y16").Formula = "=IF(ISBLANK(S16),"""",MAX(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16))"

Why not storing this formula in a (hidden) row or column, then just copying the cells if and when needed (instead of hardcoding a lot of things in the VBA code.

Wigi
Click to expand...

Good call on the selects. I don't know why I did that...

I'm doing it in VBA because I have to insert variables that are driven by combo boxes.
 
Upvote 0
Place
Code: 
Application.ScreenUpdating = False
at the front of the code

and
Code: 
Application.ScreenUpdating = True
at the end of the code.

This disables the spreadsheet update while macro is being run.

You might also want to disable
Code: 
Application.Calculation
as well.

by using
Code: 
Application.Calculation = xlCalculationManual
then turning it back on at the end of the code
Code: 
Application.Calculation = xlCalculationAutomatic
 
Upvote 0
Kpark, did you read line 2 in the opening post?

Also, "Application.ScreenUpdating = True" at the end is not needed.

Wigi
 
Upvote 0
Try to set ActiveSheet as a constant and other values as a constant.
Altho this will not have SIGNIFICANT impact on the runtime, it will optimize the code.
 
Upvote 0
kpark91 said:
Try to set ActiveSheet as a constant and other values as a constant.
Altho this will not have SIGNIFICANT impact on the runtime, it will optimize the code.
Click to expand...

Indeed, removing all the interactions with the sheet as much as possible, will yield more significant time reductions.
 
Upvote 0
wigi said:
Kpark, did you read line 2 in the opening post?

Also, "Application.ScreenUpdating = True" at the end is not needed.

Wigi
Click to expand...

While "Application.ScreenUpdating = True" may not be needed at the end, I would highly recommend that you explicitly declare it back to true. There have been times where I did not set it to True, and Excel would not seem to respond until I went to the immediate window and changed it back to true.

Basically, it is like explicitly declaring every one of your variables... while not necessary, it just saves some headache in the long run. ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Is there a way to speed up this SumIf VBA code?
Replies
1
Views
402
DanteAmor
DanteAmor
N
  • Question Question
Easiest way to get total.
Replies
3
Views
488
awoohaw
awoohaw
D
  • Question Question
Graph/Chart with Multiple Dropdown
Replies
0
Views
450
davidmor
D
M
  • Question Question
If & And Condition based on lookup value change at every 30 row
Replies
2
Views
407
mba_110
M
L
  • Question Question
INDEX / MATCH Used Horizontally & Looking for a Value Based on a Dropdown List
Replies
1
Views
676
LucieLiskova
L

Forum statistics

Threads
1,224,503
Messages
6,179,136
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top