PDA

View Full Version : Loop to set 250 strings to cell contents



b.hill
08-30-2014, 08:53 AM
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
08-30-2014, 09:21 AM
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
08-30-2014, 10:23 AM
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
08-30-2014, 11:37 AM
or


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

Jan Karel Pieterse
09-01-2014, 03:44 AM
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
09-02-2014, 08:42 AM
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??