chineth
04-30-2012, 09:34 AM
Hi,
I am trying to go through a list of items and have them be stored in a dynamic array. Then that list would look like 'A, B, C, D'... in a specified cell. I'm not sure how to make a string 'dynamic'.
I have this code so far for 20 at a time:
Dim arr(1 To 20) As Variant, rng As Range, id As String, rng2 As Range
Range("b2").Activate
Do Until IsEmpty(ActiveCell)
For i = 1 To 20 '20
If i = 1 Then
Set rng = ActiveCell.Offset(0, 1)
End If
arr(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next i
Set rng2 = ActiveCell
id = arr(1) + ", " + arr(2) + ", " + arr(3) + ", " + arr(4) + ", " + arr(5) + ", " + arr(6) + ", " + arr(7) + ", " + arr(8) + ", " + arr(9) + ", " + arr(10) + ", " + arr(11) + ", " + arr(12) + ", " + arr(13) + ", " + arr(14) + ", " + arr(15) + ", " + arr(16) + ", " + arr(17) + ", " + arr(18) + ", " + arr(19) + ", " + arr(20)
rng.Value = id
Loop
Clearly, this hardcode is not adaptable, so how could this to a nonspecified number?
Thank you for your input!
I am trying to go through a list of items and have them be stored in a dynamic array. Then that list would look like 'A, B, C, D'... in a specified cell. I'm not sure how to make a string 'dynamic'.
I have this code so far for 20 at a time:
Dim arr(1 To 20) As Variant, rng As Range, id As String, rng2 As Range
Range("b2").Activate
Do Until IsEmpty(ActiveCell)
For i = 1 To 20 '20
If i = 1 Then
Set rng = ActiveCell.Offset(0, 1)
End If
arr(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next i
Set rng2 = ActiveCell
id = arr(1) + ", " + arr(2) + ", " + arr(3) + ", " + arr(4) + ", " + arr(5) + ", " + arr(6) + ", " + arr(7) + ", " + arr(8) + ", " + arr(9) + ", " + arr(10) + ", " + arr(11) + ", " + arr(12) + ", " + arr(13) + ", " + arr(14) + ", " + arr(15) + ", " + arr(16) + ", " + arr(17) + ", " + arr(18) + ", " + arr(19) + ", " + arr(20)
rng.Value = id
Loop
Clearly, this hardcode is not adaptable, so how could this to a nonspecified number?
Thank you for your input!