VBA Case statement

ExcelNovice

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
I'm using Case Statement in my VBA, but it works inconsistently.
As I'm working on the spreadsheet, updating data etc, the cells will sometimes update as expected, and sometimes will remain blank. How can I fix this?
See example of the code below. Thanks....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Select Case Range("AL45").Value
Case 1
Range("ad12").Value = Range("ag45").Value
Range("ad13").Value = Range("aj45").Value
Range("ad14").Value = Range("ac121").Value
Range("ad15").Value = Range("ad4").Value

Case 2
Range("ae12").Value = Range("ag45").Value
Range("ae13").Value = Range("aj45").Value
Range("ae14").Value = Range("ac122").Value
Range("ae15").Value = Range("ae4").Value

Case 3
Range("af12").Value = Range("ag45").Value
Range("af13").Value = Range("aj45").Value
Range("af14").Value = Range("ac123").Value
Range("af15").Value = Range("af4").Value

Case 4
Range("ag12").Value = Range("ag45").Value
Range("ag13").Value = Range("aj45").Value
Range("ag14").Value = Range("ac124").Value
Range("ag15").Value = Range("ag4").Value

Case 5
Range("ah12").Value = Range("ag45").Value
Range("ah13").Value = Range("aj45").Value
Range("ah14").Value = Range("ac125").Value
Range("ah15").Value = Range("ah4").Value

Case 6
Range("ai12").Value = Range("ag45").Value
Range("ai13").Value = Range("aj45").Value
Range("ai14").Value = Range("ac126").Value
Range("ai15").Value = Range("ai4").Value

Case 7
Range("aj12").Value = Range("ag45").Value
Range("aj13").Value = Range("aj45").Value
Range("aj14").Value = Range("ac127").Value
Range("aj15").Value = Range("aj4").Value

Case 8
Range("ak12").Value = Range("ag45").Value
Range("ak13").Value = Range("aj45").Value
Range("ak14").Value = Range("ac128").Value
Range("ak15").Value = Range("ak4").Value

Case 9
Range("al12").Value = Range("ag45").Value
Range("al13").Value = Range("aj45").Value
Range("al14").Value = Range("ac129").Value
Range("al15").Value = Range("al4").Value

Case 10
Range("am12").Value = Range("ag45").Value
Range("am13").Value = Range("aj45").Value
Range("am14").Value = Range("ac130").Value
Range("am15").Value = Range("am4").Value

Case 11
Range("an12").Value = Range("ag45").Value
Range("an13").Value = Range("aj45").Value
Range("an14").Value = Range("ac131").Value
Range("an15").Value = Range("an4").Value

Case 12
Range("ao12").Value = Range("ag45").Value
Range("ao13").Value = Range("aj45").Value
Range("ao14").Value = Range("ac132").Value
Range("ao15").Value = Range("ao4").Value

Case 13
Range("ap12").Value = Range("ag45").Value
Range("ap13").Value = Range("aj45").Value
Range("ap14").Value = Range("ac133").Value
Range("ap15").Value = Range("ap4").Value

Case 14
Range("aq12").Value = Range("ag45").Value
Range("aq13").Value = Range("aj45").Value
Range("aq14").Value = Range("ac134").Value
Range("aq15").Value = Range("aq4").Value

Case 15
Range("ar12").Value = Range("ag45").Value
Range("ar13").Value = Range("aj45").Value
Range("ar14").Value = Range("ac135").Value
Range("ar15").Value = Range("ar4").Value

Case 16
Range("as12").Value = Range("ag45").Value
Range("as13").Value = Range("aj45").Value
Range("as14").Value = Range("ac136").Value
Range("as15").Value = Range("as4").Value

Case 17
Range("at12").Value = Range("ag45").Value
Range("at13").Value = Range("aj45").Value
Range("at14").Value = Range("ac137").Value
Range("at15").Value = Range("at4").Value

Case 18
Range("au12").Value = Range("ag45").Value
Range("au13").Value = Range("aj45").Value
Range("au14").Value = Range("ac138").Value
Range("au15").Value = Range("au4").Value

Case 19
Range("av12").Value = Range("ag45").Value
Range("av13").Value = Range("aj45").Value
Range("av14").Value = Range("ac139").Value
Range("av15").Value = Range("av4").Value

Case 20
Range("aw12").Value = Range("ag45").Value
Range("aw13").Value = Range("aj45").Value
Range("aw14").Value = Range("ac140").Value
Range("aw15").Value = Range("aw4").Value

Case 21
Range("ax12").Value = Range("ag45").Value
Range("ax13").Value = Range("aj45").Value
Range("ax14").Value = Range("ac141").Value
Range("ax15").Value = Range("ax4").Value

Case 22
Range("ay12").Value = Range("ag45").Value
Range("ay13").Value = Range("aj45").Value
Range("ay14").Value = Range("ac142").Value
Range("ay15").Value = Range("ay4").Value

Case 23
Range("az12").Value = Range("ag45").Value
Range("az13").Value = Range("aj45").Value
Range("az14").Value = Range("ac143").Value
Range("az15").Value = Range("az4").Value

Case 24
Range("ba12").Value = Range("ag45").Value
Range("ba13").Value = Range("aj45").Value
Range("ba14").Value = Range("ac144").Value
Range("ba15").Value = Range("ba4").Value

Case 25
Range("bb12").Value = Range("ag45").Value
Range("bb13").Value = Range("aj45").Value
Range("bb14").Value = Range("ac145").Value
Range("bb15").Value = Range("bb4").Value

Case 26
Range("bc12").Value = Range("ag45").Value
Range("bc13").Value = Range("aj45").Value
Range("bc14").Value = Range("ac146").Value
Range("bc15").Value = Range("bc4").Value

Case 27
Range("bd12").Value = Range("ag45").Value
Range("bd13").Value = Range("aj45").Value
Range("bd14").Value = Range("ac147").Value
Range("bd15").Value = Range("bd4").Value

Case 28
Range("be12").Value = Range("ag45").Value
Range("be13").Value = Range("aj45").Value
Range("be14").Value = Range("ac148").Value
Range("be15").Value = Range("be4").Value

Case 29
Range("bf12").Value = Range("ag45").Value
Range("bf13").Value = Range("aj45").Value
Range("bf14").Value = Range("ac149").Value
Range("bf15").Value = Range("bf4").Value

Case 30
Range("bg12").Value = Range("ag45").Value
Range("bg13").Value = Range("aj45").Value
Range("bg14").Value = Range("ac150").Value
Range("bg15").Value = Range("bg4").Value

Case 31
Range("bh12").Value = Range("ag45").Value
Range("bh13").Value = Range("aj45").Value
Range("bh14").Value = Range("ac151").Value
Range("bh15").Value = Range("bh4").Value

Case 32
Range("bi12").Value = Range("ag45").Value
Range("bi13").Value = Range("aj45").Value
Range("bi14").Value = Range("ac152").Value
Range("bi15").Value = Range("bi4").Value

Case 33
Range("bj12").Value = Range("ag45").Value
Range("bj13").Value = Range("aj45").Value
Range("bj14").Value = Range("ac153").Value
Range("bj15").Value = Range("bj4").Value

Case 34
Range("bk12").Value = Range("ag45").Value
Range("bk13").Value = Range("aj45").Value
Range("bk14").Value = Range("ac154").Value
Range("bk15").Value = Range("bk4").Value

Case 35
Range("bl12").Value = Range("ag45").Value
Range("bl13").Value = Range("aj45").Value
Range("bl14").Value = Range("ac155").Value
Range("bl15").Value = Range("bl4").Value

Case 36
Range("bm12").Value = Range("ag45").Value
Range("bm13").Value = Range("aj45").Value
Range("bm14").Value = Range("ac156").Value
Range("bm15").Value = Range("bm4").Value

Case 37
Range("bn12").Value = Range("ag45").Value
Range("bn13").Value = Range("aj45").Value
Range("bn14").Value = Range("ac157").Value
Range("bn15").Value = Range("bn4").Value

Case 38
Range("bo12").Value = Range("ag45").Value
Range("bo13").Value = Range("aj45").Value
Range("bo14").Value = Range("ac158").Value
Range("bo15").Value = Range("bo4").Value

Case 39
Range("bp12").Value = Range("ag45").Value
Range("bp13").Value = Range("aj45").Value
Range("bp14").Value = Range("ac159").Value
Range("bp15").Value = Range("bp4").Value

Case 40
Range("bq12").Value = Range("ag45").Value
Range("bq13").Value = Range("aj45").Value
Range("bq14").Value = Range("ac160").Value
Range("bq15").Value = Range("bq4").Value

Case 41
Range("br12").Value = Range("ag45").Value
Range("br13").Value = Range("aj45").Value
Range("br14").Value = Range("ac161").Value
Range("br15").Value = Range("br4").Value

Case 42
Range("bs12").Value = Range("ag45").Value
Range("bs13").Value = Range("aj45").Value
Range("bs14").Value = Range("ac162").Value
Range("bs15").Value = Range("bs4").Value

Case 43
Range("bt12").Value = Range("ag45").Value
Range("bt13").Value = Range("aj45").Value
Range("bt14").Value = Range("ac163").Value
Range("bt15").Value = Range("bt4").Value

Case 44
Range("bu12").Value = Range("ag45").Value
Range("bu13").Value = Range("aj45").Value
Range("bu14").Value = Range("ac164").Value
Range("bu15").Value = Range("bu4").Value

Case 45
Range("bv12").Value = Range("ag45").Value
Range("bv13").Value = Range("aj45").Value
Range("bv14").Value = Range("ac165").Value
Range("bv15").Value = Range("bv4").Value

Case 46
Range("bw12").Value = Range("ag45").Value
Range("bw13").Value = Range("aj45").Value
Range("bw14").Value = Range("ac166").Value
Range("bw15").Value = Range("bw4").Value

Case 47
Range("bx12").Value = Range("ag45").Value
Range("bx13").Value = Range("aj45").Value
Range("bx14").Value = Range("ac167").Value
Range("bx15").Value = Range("bx4").Value

Case 48
Range("by12").Value = Range("ag45").Value
Range("by13").Value = Range("aj45").Value
Range("by14").Value = Range("ac168").Value
Range("by15").Value = Range("by4").Value

Case 49
Range("bz12").Value = Range("ag45").Value
Range("bz13").Value = Range("aj45").Value
Range("bz14").Value = Range("ac169").Value
Range("bz15").Value = Range("bz4").Value

Case 50
Range("ca12").Value = Range("ag45").Value
Range("ca13").Value = Range("aj45").Value
Range("ca14").Value = Range("ac170").Value
Range("ca15").Value = Range("ca4").Value

End Select
 
Sort by date Sort by votes
Yes, it's running continually and that is definitely the problem.
I guess I'll have to go back to using formulas.

Thanks for your help.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
See if this helps as it captures the calculation method, stores it and sets the workbook calculation method to manual before the code is run. It then resets it to what it was after the code is finished so it may break the loop (besides that I'm out of ideas I'm afraid :():

Code: 
Option Explicit
Private Sub Worksheet_Calculate()

    Dim lngMyCol As Long
    Dim lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    Select Case Val(Range("AL45"))
        Case 1 To 50
            lngMyCol = 29 + Val(Range("AL45")) 'Col AD (the first column to be populated) is the 30th column
            Cells(12, lngMyCol).Value = Range("AG45").Value
            Cells(13, lngMyCol).Value = Range("AJ45").Value
            lngMyRow = 120 + Val(Range("AL45")) 'Row number for Col. AC
            Cells(14, lngMyCol).Value = Cells(lngMyRow, "AC")
            Cells(15, lngMyCol).Value = Cells(4, lngMyCol)
    End Select
    
    With Application
        .EnableEvents = True
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub

Robert
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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