Array question

[mickiedevries]

I need help in my program, what I need to do is assign variables to a
array and then have the array display on a worksheet one element of th
array to a row.

For example if option one is selected then variables A, B, and C nee
to be assigned to the same array. The problem is I don't know what th
total number of elements that will need to be stored in the array wil
be because it is dependent on the user's choices and will change eac
time the program is ran. I have been reading about ReDim Preserve, bu
I can't put it all together yet to work.

Then the total elements must display on the worksheet with for exampl
the array element containing variable A on line one, variable B on lin
two, etc. until all the elements in the array are displayed. I'm new a
this so I'm stuck

Thanks for any help

Micki

 

[Jenny]

Greetings. I think this is what you need (basically).
Insert a module into your project and copy this code to
that module. You can modify this basic code to fit your
needs.

----------------------------------------------------------

Option Explicit

'this is the array which contains your data. I did not
'set the number of elements because they can
'change at any time
Public VariableArray() As String

Public Sub DisplayArrayInfo()
'generic counting variable
Dim Counter As Long

'redimension the array (preserve is used to maintain
'the data currently in the array. Leave this out if
'you want the array erased when it is redimensioned)
ReDim Preserve VariableArray(10) As String

'set the variable data. This can take place anywyere,
'but I put it here for simplicity
VariableArray(0) = "Hello"
VariableArray(1) = "Good bye"
VariableArray(2) = "2"
VariableArray(3) = "3"
VariableArray(4) = "4"
VariableArray(5) = "5"
VariableArray(6) = "6"
VariableArray(7) = "7"
VariableArray(8) = "8"
VariableArray(9) = "9"
VariableArray(10) = "10"

'run through a loop from 0 to the upper bound of the
'array and add the array values to Sheet1
For Counter = 0 To UBound(VariableArray)
Sheets("Sheet1").Cells(Counter + 1, "A").Value =
VariableArray(Counter)
Next Counter
End Sub

--------------------------------------------------------

By the way, this text is formatted to look nice on this
screen, but you may have to clean it up a bit when you
copy it.

~Jenny

 

[Tom Ogilvy]

Dim myArray()
redim myArray(0 to 0)

do
res = Inputbox("Enter a Letter, click cancel to quit")
if res = "" then exit do
if not isempty(myArray(ubound)) then
redim preserve MyArray(0 to ubound(myArray) + 1)
end if
myArray(ubound(myArray)) = res
Loop While True

for i = lbound(myArray) to ubound(myArray)
Range("A1").offset(i,0).Value = myArray(i)
Next


There are other ways, like in the initial redim, making an array larger than
you need, then after looping, redim preserve it down to the size you used.
You can also use application.Transpose to put the array down on the
worksheet in on command. But this should get you started.

 

[mickiedevries]

Thanks,

This should help me get started!

Micki