Declared variable based on cell value

[ChristineJ]

The code below enters the values in the array "entryCells" into range Range("A1:E1"). I need to expand this so that the values entered into Range("A1:E1") vary based on the value in cell B10 (which in this short example would range from 1 to 5).

For example, if the value in B10 is 2, the values entered in Range("A1:E1") would be "D29", "D30", "D31", "D32", "D33" from 'entryCells2 = Array("D29", "D30", "D31", "D32", "D33") shown below (as a comment). Can this be modified? Thanks!

Sub Test() Dim entryCells As Variant entryCells = Array("B24", "B26", "B28", "B30", "B32") 'entryCells1 = Array("B24", "B26", "B28", "B30", "B32") 'entryCells2 = Array("D29", "D30", "D31", "D32", "D33") 'entryCells3 = Array("S96", "S97", "S99", "S100", "U95") 'entryCells4 = Array("H35", "H37", "H38", "O40", "O41") 'entryCells5= Array("S122", "S123", "S124", "U117", "U118") Range("A1:E1").Value = entryCells End Sub

 

[Norie]

You could use a Select...Case statement.

Sub Test()
Dim entryCells As Variant

    Select Case Range("B10").Value

        Case 1
            entryCells = Array("B24", "B26", "B28", "B30", "B32")
        Case 2
            entryCells = Array("D29", "D30", "D31", "D32", "D33")
        Case 3
            entryCells = Array("S96", "S97", "S99", "S100", "U95")
        Case 4
            entryCells = Array("H35", "H37", "H38", "O40", "O41")
        Case 5
            entryCells = Array("S122", "S123", "S124", "U117", "U118")
    End Select

    Range("A1:E1").Value = entryCells

End Sub

 

[Fluff]

Another option

Sub Test()

Dim entryCells As Variant

entryCells = Array(Array("B24", "B26", "B28", "B30", "B32"), _
                   Array("D29", "D30", "D31", "D32", "D33"), _
                   Array("S96", "S97", "S99", "S100", "U95"), _
                   Array("H35", "H37", "H38", "O40", "O41"), _
                   Array("S122", "S123", "S124", "U117", "U118"))

Range("A1:E1").Value = entryCells(Range("B10") - 1)

End Sub

 

[ChristineJ]

Both are excellent solutions and work perfectly! Just what I needed. Thank you both for your quick replies.

 

[Fluff]

Glad we could help & thanks for the feedback.