VBA Case statement

[ExcelNovice]

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

 

[eduzs]

Probably Range("AL45").Value is not between 1 and 50, or the "X" is empty, ...value= Range(X).value

 

[ExcelNovice]

Conditions are met, but it still doesn't work all the time.

 

[Trebor76]

How is the value in cell AL45 derived - via data entry or a formula?

 

[ExcelNovice]

Formula based, and the results changes frequently, after just about every 7th 14th entry (data input) on the spreadsheet.

Formula: =SUM(IN122:IN171)

 

[Trebor76]

You're using the wrong event then - it has to the Worksheet_Calculate event. You also don't need to go through each case like you're doing:

Option Explicit
Private Sub Worksheet_Calculate()

    Dim lngMyCol As Long
    Dim lngMyRow As Long
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    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
        .ScreenUpdating = True
    End With

End Sub

HTH

Robert

 

[ExcelNovice]

Thanks for your help Trebor76, well appreciated.
It's a much shorter code, but I really don't understand it. Do I need to add anything to the code for it to work? What would I need to change if I extend the data in column AC for instance to include AC171, AC172, AC173?

Thanks again.

 

[Trebor76]

Do I need to add anything to the code for it to work?

No. Just try and see how it goes.

What would I need to change if I extend the data in column AC for instance to include AC171, AC172, AC173?

You shouldn't have to change anything. The lngMyCol and lngMyRow variables automatically adjust by adding the value in cell AL45 to their starting point (29 and 120 respectively).

HTH

Robert

 

[ExcelNovice]

Great, thank you Trebor76.

I ran into another problem when I changed the event.
The message being received now is "Out of Memory".

 

[Trebor76]

I ran into another problem when I changed the event.
The message being received now is "Out of Memory".

At a guess I'd say that's because it's almost continually running. Are there any other events? Are you sure you can't have a formula solution?