Loop to set 250 strings to cell contents

[b.hill]

I need a loop to set 250 strings equal to the contents of the first 250 cells in row 2. I am trying to get each string to be called Cel1, Cel2, Cel3....Cel250 with a loop instead of pasting it 250 times (I am getting a "Procedure too large" error when I try to run it this way).

Dim pk as Integer
Dim num as Integer
Dim r as Integer

pk = 1
num = 1
r = 2

For pk = 1 To 250  
  Cel & num = Cells(1, r).Value   '''getting expected expression error with this line
  num = num + 1
  r = r + 1
Next pk

I can't figure out a way to combine "Cel" with num to set the correct string name for each loop. Thanks in advance.

[JKwan]

I may be wrong but your line where your error occurred.... the left hand side is your variable and you cannot do it like that. You can set it up as an array, I think that is what you after

  dim cel(250) as string

   cel(pk) = Cells(1, r).Value

[SamT]

You can't* name variables programatically. Ie Dim Cel1, Dim Cel2, Dim Cel3.

I would use an array like JKwan said, with one small change

Dim cel(250) As String 
 For pk = 0 to 249
cel(pk) = Cells(2, pk + 2).Value 'Row 2, Column pk + 2

cel(pk) = Cells(pk + 2, 2).Value 'Row pk+2, Column "B"

If you are not storing the Cel(n) = Value(n) strings as text somewhere, just retrieve the values from the Array.

To create strings to store as text from the values in the Array

For i = 0 To 249
String = "Cel" & CString(i + 1) & " = " & Array(i)

*You can use a loop to write variable names in an entire code module that is created programatically, but that is a VBIDE lesson for another day.

[snb]

or

Sub M_snb()
    [a2:IP2] = [index("snb_"&A1:IP1,)]
End Sub

[Jan Karel Pieterse]

You can assign the value of all cells to an array in one fell swoop:

Sub GetDataFromExcel()
    Dim vArray As Variant
    vArray = Range("A1:A250").Value
    'Now vArray is a 1 to 250, 1 to 1 dimensioned array (250 rows, 1 column) containing the values of the cells
End Sub

[Paul_Hossler]

I need a loop to set 250 strings equal to the contents of the first 250 cells in row 2. I am trying to get each string to be called Cel1, Cel2, Cel3....Cel250

Why do you want to do that? It sounds like you want to have 250 separately Dim-ed string variables, and that you're not putting data into other cells on a worksheet????

I'd think that they'd be very hard to manipulate.

The array suggestions would be better

But what is the larger objective that you want to accomplish??