Adding ranges with VBA code depending on Form Control

**esbencito** · 10-24-2017, 04:21 AM

Hi all,

Is there a particular reason why below code runs for 25 seconds? Can it be optimised somehow? The range aren't calculating anything, so far I have simply "added" them to the workbook...

Sub TimePeriod_AnalyticsComboBox_Change()
    
    Application.ScreenUpdating = False
    
    Select Case Worksheets("Control").Range("Fiscal_Year_Analytics")
        Case Is = "FY 2016"
        
                    ActiveWorkbook.Names.Add "APR", "=""" & Range("$V$5:$V$2500").Address & """"
                    ActiveWorkbook.Names.Add "MAY", "=""" & Range("$W$5:$W$2500").Address & """"
                    ActiveWorkbook.Names.Add "JUN", "=""" & Range("$X$5:$X$2500").Address & """"
                    ActiveWorkbook.Names.Add "JUL", "=""" & Range("$Y$5:$Y$2500").Address & """"
                    ActiveWorkbook.Names.Add "AUG", "=""" & Range("$Z$5:$Z$2500").Address & """"
                    ActiveWorkbook.Names.Add "SEP", "=""" & Range("$AA$5:$AA$2500").Address & """"
                    ActiveWorkbook.Names.Add "OCT", "=""" & Range("$AB$5:$AB$2500").Address & """"
                    ActiveWorkbook.Names.Add "NOV", "=""" & Range("$AC$5:$AC$2500").Address & """"
                    ActiveWorkbook.Names.Add "DEC", "=""" & Range("$AD$5:$AD$2500").Address & """"
                    ActiveWorkbook.Names.Add "JAN", "=""" & Range("$AE$5:$AE$2500").Address & """"
                    ActiveWorkbook.Names.Add "FEB", "=""" & Range("$AF$5:$AF$2500").Address & """"
                    ActiveWorkbook.Names.Add "MAR", "=""" & Range("$AG$5:$AG$2500").Address & """"
                    
                    ActiveWorkbook.Names.Add "APR_LY", "=""" & Range("$V$5:$V$2500").Address & """"
                    ActiveWorkbook.Names.Add "MAY_LY", "=""" & Range("$W$5:$W$2500").Address & """"
                    ActiveWorkbook.Names.Add "JUN_LY", "=""" & Range("$X$5:$X$2500").Address & """"
                    ActiveWorkbook.Names.Add "JUL_LY", "=""" & Range("$Y$5:$Y$2500").Address & """"
                    ActiveWorkbook.Names.Add "AUG_LY", "=""" & Range("$Z$5:$Z$2500").Address & """"
                    ActiveWorkbook.Names.Add "SEP_LY", "=""" & Range("$AA$5:$AA$2500").Address & """"
                    ActiveWorkbook.Names.Add "OCT_LY", "=""" & Range("$AB$5:$AB$2500").Address & """"
                    ActiveWorkbook.Names.Add "NOV_LY", "=""" & Range("$AC$5:$AC$2500").Address & """"
                    ActiveWorkbook.Names.Add "DEC_LY", "=""" & Range("$AD$5:$AD$2500").Address & """"
                    ActiveWorkbook.Names.Add "JAN_LY", "=""" & Range("$AE$5:$AE$2500").Address & """"
                    ActiveWorkbook.Names.Add "FEB_LY", "=""" & Range("$AF$5:$AF$2500").Address & """"
                    ActiveWorkbook.Names.Add "MAR_LY", "=""" & Range("$AG$5:$AG$2500").Address & """"

            End Select
            
    Select Case Worksheets("Control").Range("Fiscal_Year_Analytics")
        Case Is = "FY 2017"
        
                    ActiveWorkbook.Names.Add "APR", "=""" & Range("$V$5:$V$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "MAY", "=""" & Range("$W$5:$W$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JUN", "=""" & Range("$X$5:$X$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JUL", "=""" & Range("$Y$5:$Y$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "AUG", "=""" & Range("$Z$5:$Z$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "SEP", "=""" & Range("$AA$5:$AA$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "OCT", "=""" & Range("$AB$5:$AB$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "NOV", "=""" & Range("$AC$5:$AC$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "DEC", "=""" & Range("$AD$5:$AD$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JAN", "=""" & Range("$AE$5:$AE$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "FEB", "=""" & Range("$AF$5:$AF$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "MAR", "=""" & Range("$AG$5:$AG$2500").Offset(0, 21).Address & """"
                    
                    ActiveWorkbook.Names.Add "APR_LY", "=""" & Range("$V$5:$V$2500").Address & """"
                    ActiveWorkbook.Names.Add "MAY_LY", "=""" & Range("$W$5:$W$2500").Address & """"
                    ActiveWorkbook.Names.Add "JUN_LY", "=""" & Range("$X$5:$X$2500").Address & """"
                    ActiveWorkbook.Names.Add "JUL_LY", "=""" & Range("$Y$5:$Y$2500").Address & """"
                    ActiveWorkbook.Names.Add "AUG_LY", "=""" & Range("$Z$5:$Z$2500").Address & """"
                    ActiveWorkbook.Names.Add "SEP_LY", "=""" & Range("$AA$5:$AA$2500").Address & """"
                    ActiveWorkbook.Names.Add "OCT_LY", "=""" & Range("$AB$5:$AB$2500").Address & """"
                    ActiveWorkbook.Names.Add "NOV_LY", "=""" & Range("$AC$5:$AC$2500").Address & """"
                    ActiveWorkbook.Names.Add "DEC_LY", "=""" & Range("$AD$5:$AD$2500").Address & """"
                    ActiveWorkbook.Names.Add "JAN_LY", "=""" & Range("$AE$5:$AE$2500").Address & """"
                    ActiveWorkbook.Names.Add "FEB_LY", "=""" & Range("$AF$5:$AF$2500").Address & """"
                    ActiveWorkbook.Names.Add "MAR_LY", "=""" & Range("$AG$5:$AG$2500").Address & """"

            End Select
            
    Select Case Worksheets("Control").Range("Fiscal_Year_Analytics")
        Case Is = "PLAN 18"
        
                    ActiveWorkbook.Names.Add "APR", "=""" & Range("$V$5:$V$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "MAY", "=""" & Range("$W$5:$W$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "JUN", "=""" & Range("$X$5:$X$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "JUL", "=""" & Range("$Y$5:$Y$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "AUG", "=""" & Range("$Z$5:$Z$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "SEP", "=""" & Range("$AA$5:$AA$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "OCT", "=""" & Range("$AB$5:$AB$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "NOV", "=""" & Range("$AC$5:$AC$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "DEC", "=""" & Range("$AD$5:$AD$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "JAN", "=""" & Range("$AE$5:$AE$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "FEB", "=""" & Range("$AF$5:$AF$2500").Offset(0, 42).Address & """"
                    ActiveWorkbook.Names.Add "MAR", "=""" & Range("$AG$5:$AG$2500").Offset(0, 42).Address & """"
                    
                    ActiveWorkbook.Names.Add "APR_LY", "=""" & Range("$V$5:$V$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "MAY_LY", "=""" & Range("$W$5:$W$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JUN_LY", "=""" & Range("$X$5:$X$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JUL_LY", "=""" & Range("$Y$5:$Y$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "AUG_LY", "=""" & Range("$Z$5:$Z$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "SEP_LY", "=""" & Range("$AA$5:$AA$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "OCT_LY", "=""" & Range("$AB$5:$AB$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "NOV_LY", "=""" & Range("$AC$5:$AC$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "DEC_LY", "=""" & Range("$AD$5:$AD$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JAN_LY", "=""" & Range("$AE$5:$AE$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "FEB_LY", "=""" & Range("$AF$5:$AF$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "MAR_LY", "=""" & Range("$AG$5:$AG$2500").Offset(0, 21).Address & """"

            End Select
    
    Select Case Worksheets("Control").Range("Fiscal_Year_Analytics")
        Case Is = "FY 2018"
        
                    ActiveWorkbook.Names.Add "APR", "=""" & Range("$V$5:$V$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "MAY", "=""" & Range("$W$5:$W$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "JUN", "=""" & Range("$X$5:$X$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "JUL", "=""" & Range("$Y$5:$Y$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "AUG", "=""" & Range("$Z$5:$Z$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "SEP", "=""" & Range("$AA$5:$AA$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "OCT", "=""" & Range("$AB$5:$AB$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "NOV", "=""" & Range("$AC$5:$AC$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "DEC", "=""" & Range("$AD$5:$AD$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "JAN", "=""" & Range("$AE$5:$AE$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "FEB", "=""" & Range("$AF$5:$AF$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "MAR", "=""" & Range("$AG$5:$AG$2500").Offset(0, 63).Address & """"
                    
                    ActiveWorkbook.Names.Add "APR_LY", "=""" & Range("$V$5:$V$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "MAY_LY", "=""" & Range("$W$5:$W$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JUN_LY", "=""" & Range("$X$5:$X$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JUL_LY", "=""" & Range("$Y$5:$Y$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "AUG_LY", "=""" & Range("$Z$5:$Z$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "SEP_LY", "=""" & Range("$AA$5:$AA$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "OCT_LY", "=""" & Range("$AB$5:$AB$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "NOV_LY", "=""" & Range("$AC$5:$AC$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "DEC_LY", "=""" & Range("$AD$5:$AD$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "JAN_LY", "=""" & Range("$AE$5:$AE$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "FEB_LY", "=""" & Range("$AF$5:$AF$2500").Offset(0, 21).Address & """"
                    ActiveWorkbook.Names.Add "MAR_LY", "=""" & Range("$AG$5:$AG$2500").Offset(0, 21).Address & """"

            End Select

    Select Case Worksheets("Control").Range("Fiscal_Year_Analytics")
        Case Is = "FY 2019"
        
                    ActiveWorkbook.Names.Add "APR", "=""" & Range("$V$5:$V$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "MAY", "=""" & Range("$W$5:$W$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "JUN", "=""" & Range("$X$5:$X$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "JUL", "=""" & Range("$Y$5:$Y$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "AUG", "=""" & Range("$Z$5:$Z$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "SEP", "=""" & Range("$AA$5:$AA$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "OCT", "=""" & Range("$AB$5:$AB$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "NOV", "=""" & Range("$AC$5:$AC$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "DEC", "=""" & Range("$AD$5:$AD$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "JAN", "=""" & Range("$AE$5:$AE$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "FEB", "=""" & Range("$AF$5:$AF$2500").Offset(0, 84).Address & """"
                    ActiveWorkbook.Names.Add "MAR", "=""" & Range("$AG$5:$AG$2500").Offset(0, 84).Address & """"
                    
                    ActiveWorkbook.Names.Add "APR_LY", "=""" & Range("$V$5:$V$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "MAY_LY", "=""" & Range("$W$5:$W$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "JUN_LY", "=""" & Range("$X$5:$X$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "JUL_LY", "=""" & Range("$Y$5:$Y$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "AUG_LY", "=""" & Range("$Z$5:$Z$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "SEP_LY", "=""" & Range("$AA$5:$AA$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "OCT_LY", "=""" & Range("$AB$5:$AB$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "NOV_LY", "=""" & Range("$AC$5:$AC$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "DEC_LY", "=""" & Range("$AD$5:$AD$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "JAN_LY", "=""" & Range("$AE$5:$AE$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "FEB_LY", "=""" & Range("$AF$5:$AF$2500").Offset(0, 63).Address & """"
                    ActiveWorkbook.Names.Add "MAR_LY", "=""" & Range("$AG$5:$AG$2500").Offset(0, 63).Address & """"

            End Select
   
    Application.ScreenUpdating = True
   
End Sub

Adding ranges with VBA code depending on Form Control

LinkBack

Thread Tools

Rate This Thread

Display

Threaded View

Adding ranges with VBA code depending on Form Control

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Call macro depending on cell link of a Form Control Box

Two Combo Boxes (Form Control) depending on each other

[SOLVED] VBA Code to have a repeating form control /Active X control (List box) in every row

Adding up ranges depending on condition

Adding a control to a User Form

Adding code to control on form causes Excel to crash

[SOLVED] Adding code to a control on a form causes excel to crash

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions