Dynamic Array

[dghillza]

Hi All,

I'm trying to create a dynamic array based on the number of populated rows.
I would then like to loop through the column and populate each element with that cells value.

So for examlpe;

Row # Value
1 10
2 19
3 22
4 6
5 12

Id what the array to have 2 dimensions to hold the row number and the value

what I have tried to do this first I count the number of rows:

i = Application.WorksheetFunction.CountA(myRange)

next I want the value of i to be the length of the array

Dim MyArray(i, 3) as Long

doesn't work - if I enter a number into i it does but then it not dependent on how many rows there are?

Any pointers in the right direction will be appreciated.

 

[RoryA]

You need to declare it as a dynamic array, then resize it:

   Dim MyArray() As Long
   i = Application.WorksheetFunction.CountA(myRange)
   ReDim MyArray(i, 3)

 

[Richard Schollar]

Hi

Why do you want to store the row number? I can't see any benefit in doing so unless perhaps you have discontinuous ranges that you want to load into the array with a record of the specific cell it came from.

If you don't need the row numbers, storing the values into an array becomes very (very) easy of course:

Dim varMyArray As Variant

varMyArray = Range("A1:A10").Value   'creates 2-dimensional array 10 elements deep, 1 element wide

For i = 1 to Ubound(varMyArray,1)
  Debug.Print varMyArray(i,1)
Next i

 

[dghillza]

Hi All

@ rorya thank you that has solved some of my problems.

@ Richard Schollar Thank you too.. I may be missing something but I copied your code and it populates the one element with the value's in the cells but I need another element with the row number. The reason for this is I want to do some sorting and calculating and need to know the row number so I can write it back to the correct row.

 

[dghillza]

Hi All here is my code, I've added comments to say what I'm doing and where I'm having problems and what I need to achieve next

Sub Temp1()

 Dim myRange As Range
 Dim MyArray() As Variant
 Dim Total As Long
 Dim Ad As Long
 
   
 Set myRange = Columns("b:b")
   x = Application.WorksheetFunction.CountA(myRange)
  ReDim MyArray(1 To x, 1 To 2)
   
' Populate the Array with the value in the cell and the row number



   For i = 1 To x
     MyArray(i, 1) = i
         MyArray(i, 2) = Cells(i, 2).Value
   Next i
    
 ' Add up the total of element 1 of the Array
    
  For i = 1 To x
        Ad = MyArray(i, 2)
        Total = Total + Ad
  Next i

'Update Element 1 with the the percentage contribution


 For i = 1 To x
       MyArray(i, 2) = Round(MyArray(i, 2) / Total * 100, 2)
  Next i

'The next thing I need to so is sort the Array in Assending order ??


End Sub