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
 
Sort by date Sort by votes

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
- What is the actual code now?
- Did you optimize the formulas? Not everything is related to VBA only.
 
Upvote 0
wigi said:
- What is the actual code now?
- Did you optimize the formulas? Not everything is related to VBA only.
Click to expand...
Just take out the selects and set the cell directly. That's the only change I made.

I don't know of any "formula" optimization I could be doing here. How would the formula make any difference anyway? It's just setting the formula to some text (a formula). Is it because of the cell calculation after the fact?

The new code:
Code: 
Sub ChangeYTDFormulas()
    Dim RowNum As Long
    'The row we're starting on
    RowNum = 16
    '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").Formula = "=IF(ISBLANK(S16),"""",MAX(INDEX(AH16:CG16,0,MATCH(CONCATENATE(""Sum Of "",YearStart),$AH$15:$CG$15,0)):CG16))"
            Range("Z16").Formula = "=IF(Y16="""","""",IF(Y16=0,"""",AA16))"
            Range("AA16").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").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").Formula = "=IF(R16="""","""",VLOOKUP(R16,$CU$16:$EI$151,38,FALSE))"
            Range("Z16").Formula = "=IF(R16="""","""",VLOOKUP(R16,$CU$16:$EI$151,39,FALSE))"

            Range("Y16:AA16").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").AutoFill Destination:=Range("V17:V" & RowNum)
            Range("X17").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").AutoFill Destination:=Range("V17:V" & RowNum)
            Range("X17").AutoFill Destination:=Range("X17:X" & RowNum)
    End Select
    Range("B15").Select
End Sub
 
Upvote 0
The more heavy the formulas, the longer it will take (ceteris paribus)...

To exaggerate, compare:

Code: 
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)))))"

to:

Code: 
Range("V17").Formula = "=1+2"

:-)

How long does it take it you recalculate in the sheet itself (with all formulas in it)?
 
Upvote 0
How long does it take to run the code if you don't do the recalculate at the end? The difference should be the time cost of the final calculation.
 
Upvote 0
In your block of code that finds the empty cell:
Code: 
    Dim RowNum As Long
    'The row we're starting on
    RowNum = 16
    '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!
You can consolidate this entire thing down to the following; completely eliminating the loop:

Code: 
Dim RowNum as Long
RowNum = Range("H16").End(xlDown).Row
However, that will stop at the FIRST blank row. If you want to set RowNum to be the last populated row in the ENTIRE column (even if there are blank cells in the middle of the data), then you want to use:

Code: 
Dim RowNum as Long
RowNum = Range("H" & Rows.Count).End(xlUp).Row
 
Upvote 0
wigi said:
How long does it take it you recalculate in the sheet itself (with all formulas in it)?
Click to expand...
Working on some tweaks...I'll post that up tomorrow.


MrKowz said:
If you want to set RowNum to be the last populated row in the ENTIRE column (even if there are blank cells in the middle of the data), then you want to use:

Code: 
Dim RowNum as Long
RowNum = Range("H" & Rows.Count).End(xlUp).Row
Click to expand...
I know about that, actually. Just found it earlier this week. However, it doesn't take very long at all to loop through like I'm doing. It's instantaneous, really.
 
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,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

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