CODEWITHSUNDEEP
vbaexcelexcel-2007
Stephenloky
Stephenloky

Reputation: 413

Dynamically create and assign values to variables?

I have 264 columns of data that I need to set each one indivudually as a Range to store the value in a variable ... so im thinking as :

For i = 1 To 5

If i = 1 Then
    search1 = Sheets(tables).Range(Cells(2, i), Cells(200, i))
ElseIf i = 2 Then
    search2 = Sheets(tables).Range(Cells(2, i), Cells(200, i))
ElseIf i = 3 Then
    search3 = Sheets(tables).Range(Cells(2, i), Cells(200, i))
ElseIf i = 4 Then
    search4 = Sheets(tables).Range(Cells(2, i), Cells(200, i))
ElseIf i = 5 Then
    search5 = Sheets(tables).Range(Cells(2, i), Cells(200, i))
End if

Next

(I have 264 i's, so I think It's not good to do that...) How can i do this ?

I've Tried:

search & i = Sheets(tables).Range(Cells(2, i), Cells(200, i))
"Compiler error, expected expression"
search(i) = Sheets(tables).Range(Cells(2,i),Cells(200,i))
"Sub or Function not Defined"

And still don't work.

Is that possible ? Do you guys have any hints ? tricks for doing this ?

Please tell me any thoughts, I will give as much information as possible to have at least a great solution here.

Thank you.

Upvotes: 0

Views: 1612

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this code:

Sub test()
    Dim i As Integer, n As Integer
    Dim tables As String
    Dim search() As Variant

    n = 264
    ReDim search(1 To n)

    tables = "Sheet1"

    With ThisWorkbook.Sheets(tables)
        For i = 1 To n
            search(i) = .Range(.Cells(2, i), .Cells(200, i)).Value
        Next
    End With

End Sub

Upvotes: 2

Related Questions