Array question
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 -- Message posted from http://www.ExcelForum.com |
Array question
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 -----Original Message----- I need help in my program, what I need to do is assign variables to an array and then have the array display on a worksheet one element of the array to a row. For example if option one is selected then variables A, B, and C need to be assigned to the same array. The problem is I don't know what the total number of elements that will need to be stored in the array will be because it is dependent on the user's choices and will change each time the program is ran. I have been reading about ReDim Preserve, but I can't put it all together yet to work. Then the total elements must display on the worksheet with for example the array element containing variable A on line one, variable B on line two, etc. until all the elements in the array are displayed. I'm new at this so I'm stuck:) Thanks for any help Mickie --- Message posted from http://www.ExcelForum.com/ . |
Array question
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. -- Regards, Tom Ogilvy "mickiedevries " wrote in message ... I need help in my program, what I need to do is assign variables to an array and then have the array display on a worksheet one element of the array to a row. For example if option one is selected then variables A, B, and C need to be assigned to the same array. The problem is I don't know what the total number of elements that will need to be stored in the array will be because it is dependent on the user's choices and will change each time the program is ran. I have been reading about ReDim Preserve, but I can't put it all together yet to work. Then the total elements must display on the worksheet with for example the array element containing variable A on line one, variable B on line two, etc. until all the elements in the array are displayed. I'm new at this so I'm stuck:) Thanks for any help Mickie --- Message posted from http://www.ExcelForum.com/ |
Array question
|
All times are GMT +1. The time now is 11:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com