Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Dear All,
I wish to create a 3 x n multi dimensional array. I basically want to do the following: 1. Start off in cell "A1" 2. travel down through 100 cells 3. If activecell contains criteria (eg. has a certain value) then collect this value, and the value of the next 2 corresponding columns (adjacent cells). 4. Paste these 3 x n cells of data in another area of the sheet. eg.. out of the 100 cells looped, I may end up with 33 rows of data that meet the criteria, therefore have a total of 99 elements in the array. I have a good understanding of single arrays, but could somebody please guide me on such a multi-dimensional array? All the searches I have undertaken so far don't seem to illustrate this type of array population. Regards, andym |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy,
What you have described sounds to me like a two dimensional array, rows make up one dimension, with n elements , and the 3 columns are the second dimension. Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Thanks Ken ... you are probably right.
I was presuming my array was going to look like: myArr(ColA,ColB,ColC) Regards, andym Ken Johnson wrote: Hi Andy, What you have described sounds to me like a two dimensional array, rows make up one dimension, with n elements , and the 3 columns are the second dimension. Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
All assumes you have Option Base 1
You can create dynamic multi dimensional arrays, but you can only re-dim the last dimension In your case, you ideally want an x * 3 array. So you have 2 choices: 1. Transpose the array so you can dynamically add extra "records" up to the max of 33 Dim MyArray() ReDim MyArray(1 To 3, 1 To 2) 'Some code ReDim Preserve MyArray(1 To 3, 1 To 4) 2. Start off with the max size and potentially have some unfilled elelments Dim MyArray(1 To 33, 1 To 3) NickHK "andym" wrote in message oups.com... Dear All, I wish to create a 3 x n multi dimensional array. I basically want to do the following: 1. Start off in cell "A1" 2. travel down through 100 cells 3. If activecell contains criteria (eg. has a certain value) then collect this value, and the value of the next 2 corresponding columns (adjacent cells). 4. Paste these 3 x n cells of data in another area of the sheet. eg.. out of the 100 cells looped, I may end up with 33 rows of data that meet the criteria, therefore have a total of 99 elements in the array. I have a good understanding of single arrays, but could somebody please guide me on such a multi-dimensional array? All the searches I have undertaken so far don't seem to illustrate this type of array population. Regards, andym |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy,
Try this Option Base 1 Public Sub Populate_2D_Array() Dim My2DArray() As Variant Dim I As Integer Dim J As Integer Dim K As Integer For I = 1 To 100 If ActiveSheet.Cells(I, 1).Value = 10 Then K = K + 1 ReDim Preserve My2DArray(K, 3) For J = 1 To 3 My2DArray(K, J) = ActiveSheet.Cells(I, J).Value Next J End If Next I MsgBox UBound(My2DArray, 1) MsgBox My2DArray(UBound(My2DArray), 3) End Sub The last two line are just to let you know the size of it first dimension, then the value of the last element for both dimensions ie last row and last column. Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy, forgot to mention the criterion for inclusion in the array was column A value = 10 Ken Johnson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Thanks Ken and Nick..
while you posting your reply I took your initial thoughts and created the following: Sub arraytest2() Dim Arr(0 To 299) As String Dim N As Integer, M As Integer Dim i As Integer, x As Integer i = 0 Range("A33").Activate For x = 1 To 100 If Left(ActiveCell.Value, 1) = 2 Then For M = 0 To 1 Arr(i) = ActiveCell.Value Arr(i + 1) = ActiveCell.Offset(0, 2).Value Arr(i + 2) = ActiveCell.Offset(0, 3).Value i = i + 3 ActiveCell.Offset(1, 0).Activate Next M End If ActiveCell.Offset(1, 0).Activate Next x For N = 0 To 299 Debug.Print Arr(M) Next N End Sub This works fine, but is obviously restrictive. I will now use your example and modify it. I appreciate everybody's help in this. Regards, andym Ken Johnson wrote: Hi Andy, forgot to mention the criterion for inclusion in the array was column A value = 10 Ken Johnson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy,
Please forgive my stupidity! As Nick stated, when using ReDim Preserve, you can only change the size of the LAST dimension, so you forget about rows staying as rows and columns staying as columns. When the code is looking at your worksheet it is always taking values from 3 columns but the number of rows it takes data from is increasing up to a final unknown number (Although it can't be bigger thwn the total number of rows being searched. Because of these facts(rows are changing, columns fixed at 3,can only resize array's 2nd dimension) you usually get your code to feed the sheet column values into the array's fixed first dimension and the sheet's row values into the array's resizeable last dimension. So values on the worksheet that appear on the worksheet as 30 rows and 3 columns would produce an array with only 3 rows and 30 columns. If you then need to place such an array back onto the worksheet you can use the Transpose worksheet function. The following code is (I hope) correct... Option Base 1 Public Sub Populate_2D_Array() Dim My2DArray() As Variant Dim I As Integer Dim J As Integer Dim K As Integer For I = 1 To 100 If ActiveSheet.Cells(I, 1).Value = 10 Then K = K + 1 ReDim Preserve My2DArray(3, K) For J = 1 To 3 My2DArray(J, K) = ActiveSheet.Cells(I, J).Value Next J End If Next I For I = 1 To UBound(My2DArray, 2) Debug.Print My2DArray(1, I) & ", " & _ My2DArray(2, I) & ", " & _ My2DArray(3, I) Next End Sub Check out the values printed in the Immediate window. Ken Johnson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy,
This version places the array values onto the worksheet starting at G1. Just change the G1 to suit your needs... Public Sub Populate_2D_Array() Dim My2DArray() As Variant Dim I As Integer Dim J As Integer Dim K As Integer For I = 1 To 100 If ActiveSheet.Cells(I, 1).Value = 10 Then K = K + 1 ReDim Preserve My2DArray(3, K) For J = 1 To 3 My2DArray(J, K) = ActiveSheet.Cells(I, J).Value Next J End If Next I ActiveSheet.Range("G1").Resize(UBound(My2DArray, 2), _ UBound(My2DArray, 1)) = WorksheetFunction.Transpose(My2DArray) End Sub Ken Johnson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy,
I've adopted your worksheet range and criterion. Again, change G1 to suit your needs... Public Sub Populate_2D_Array() Dim My2DArray() As String Dim I As Integer Dim J As Integer Dim K As Integer For I = 33 To 132 If Left(ActiveSheet.Cells(I, 1).Value, 1) = 2 Then K = K + 1 ReDim Preserve My2DArray(3, K) For J = 1 To 3 My2DArray(J, K) = ActiveSheet.Cells(I, J).Value Next J End If Next I ActiveSheet.Range("G1").Resize(UBound(My2DArray, 2), _ UBound(My2DArray, 1)) = WorksheetFunction.Transpose(My2DArray) End Sub Ken Johnson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Ken,
many thanks for your explainations and your examples. I can now get the data I require, and have a good base if I need to modify my range in the future, so I really appreciate the time you have put in. Like most occassions one solution poses another challenge!!! The range in which I offload the array is a feed for some array formulas to do some calculating. Obviously as each cell in the range is populated out of the array, the array formulas recalc causing great time delays. In my code I have added the "With Application.Calculation = xlCalculationManual" at the beginning, and have set it back to auto at the end. However, I can't get my array formulas to recalc unless I type the whole thing in again. Do you, or anybody else out there, have any solutions to this? There must be something simple I am missing!! I will add this question to a new post. Thanks again for your help. Regards, andym Ken Johnson wrote: Hi Andy, I've adopted your worksheet range and criterion. Again, change G1 to suit your needs... Public Sub Populate_2D_Array() Dim My2DArray() As String Dim I As Integer Dim J As Integer Dim K As Integer For I = 33 To 132 If Left(ActiveSheet.Cells(I, 1).Value, 1) = 2 Then K = K + 1 ReDim Preserve My2DArray(3, K) For J = 1 To 3 My2DArray(J, K) = ActiveSheet.Cells(I, J).Value Next J End If Next I ActiveSheet.Range("G1").Resize(UBound(My2DArray, 2), _ UBound(My2DArray, 1)) = WorksheetFunction.Transpose(My2DArray) End Sub Ken Johnson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi Dimensional Array
Hi Andy,
I spotted your other post and thought it a pretty interesting problem. I've tried a few things but keep on getting nowhere. How did Tim's suggestion go? My idea, which might not be doable, was for each cell in the Range("A1:C100") on sheet2 look for its dependents (cells that contain a formula that refer to it). I spotted the Dependents property in the Object Browser and Help states "Returns a Range object that represents the range containing all the dependents of a cell". However, I've never used it before and have been struggling to make use of it. My main problem is dealing with the error that occurs when there aren't any dependents. If I manage to build up a Range of all the dependents for the Range("A1:C100") I will then try the following for each dependent.. copy the formula to a string variable, then put the formula back into the dependent cell using... FormulaArray = the string variable value and having autocalculation on (but then that will probably result in the time delay again!?) I got this expression using the macro recorder for just pressing Ctrl + Shift + Enter when a cell with an array formula is entered (Selection.FormulaArray = string for formula) I could be barking up the wrong tree entirely or I'm doing something silly when I try to deal with the error resulting from asking for the dependents of a cell that has none. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi-dimensional Array Referencing | Excel Programming | |||
Multi-Dimensional Array Let & Get | Excel Programming | |||
UBound of multi-dimensional array? | Excel Programming | |||
Viewing Multi dimensional array | Excel Programming | |||
Problem with Multi-Dimensional Array | Excel Programming |