Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default Multi Dimensional Array


Hi Andy,

forgot to mention the criterion for inclusion in the array was column A
value = 10

Ken Johnson



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi-dimensional Array Referencing Fred Kalil Excel Programming 2 October 29th 05 01:46 PM
Multi-Dimensional Array Let & Get Trip[_3_] Excel Programming 0 September 21st 05 08:41 PM
UBound of multi-dimensional array? Jake Marx[_3_] Excel Programming 0 August 16th 05 06:34 PM
Viewing Multi dimensional array Codea[_2_] Excel Programming 0 August 5th 04 12:49 PM
Problem with Multi-Dimensional Array Kirk[_2_] Excel Programming 2 August 26th 03 03:31 PM


All times are GMT +1. The time now is 08:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"