Dynamic Array

[j.smith1981]

I am wanting to do a basic test with dynamic arrays.

Just to get a feel for how they work.

All I want to do is try to loop through used cells in a used range if you like.

Then the used range everytime it encounters a value, it adds 1 to the array (as this for the reason I know it must be a dynamic array), then add one the next time it encounters a value.

Just going downwards of course, later what I was wanting to do is add to a multi dimensional array for a few fields, then do some data converting on these aswell, just making a central point for all sorts of data if you like.

This is why I thought an array would be best, just lacking the basic knowledge in making it happen if you like, putting a concept into an actual program.

Any helps greatly appreciated,
Jeremy.

[Bob Phillips]

I would just drop the whole range into an array, then loop that array creatin g a new array of actual values. Looping a range would be slower.

[j.smith1981]

I would just drop the whole range into an array, then loop that array creatin g a new array of actual values. Looping a range would be slower.

Is there any tutorials for this? Struggling a bit here, can do them in PHP and all that but when it comes to VBA I get stuck, just dont have to deal with spreadsheets and arrays, prefer using php and mysql arrays.

[j.smith1981]

Hi there thanks for the theory of doing that though.

But I have got this kind of working, I em visioning a problem with the real data I am working with.

This is the code I have so far, works when I look in the locals window:

[VBA]Dim productsArray As Variant

Dim countProducts As Integer

productsArray = ActiveSheet.usedRange

countProducts = UBound(productsArray)

ReDim resizedProducts(LBound(productsArray) To UBound(productsArray)) ' literally resizes the array? ' which means this is empty!

For i = LBound(productsArray) To UBound(productsArray)
If productsArray(i) <> "" The[/VBA]

Looks like this when I go into the locals window

productsArray(1,1) = 'LC02BK'
productsArray(1,2) = '12.55'
productsArray(2,1) = '12.55'
productsArray(2,2) = '12.55'

Then just to test that it works (for a multi dimensional array, which is what would be easiest to use for me), would be like:

[VBA]
Dim productsArray As Variant

Dim countProducts As Integer

productsArray = ActiveSheet.usedRange

countProducts = UBound(productsArray)

ReDim resizedProducts(LBound(productsArray) To UBound(productsArray)) ' literally resizes the array? ' which means this is empty!

Dim i As Integer

For i = LBound(productsArray) To UBound(productsArray)
MsgBox productsArray(i, 1)
MsgBox productsArray(i, 2)
Next i
[/VBA]

I would then be able to test within that loop if that row had any NULL values or sorry "" rows containing no data.

Then filter out an array as usual later on into my import macro.

[j.smith1981]

Sorry I am quite stuck with this bit though:

[VBA]Dim productsArray As Variant

Dim countProducts As Integer

productsArray = ActiveSheet.usedRange

countProducts = UBound(productsArray)

ReDim resizedProducts(LBound(productsArray) To UBound(productsArray)) ' literally resizes the array? ' which means this is empty!

Dim i As Integer
Dim j As Integer

For i = LBound(productsArray) To UBound(productsArray)
If productsArray(i, 1) <> "" Then
'MsgBox "This is product: " & productsArray(i, 1) & "Its price is " & productsArray(i, 2)
j = j + 1
resizedProducts(j, 1) = productsArray(i, 1)

Else
MsgBox "This wont be added"
End If
Next i[/VBA]

Keeps saying on line:
[VBA]resizedProducts(j, 1) = productsArray(i, 1)[/VBA]

The error comes up as:

Run-time error '9':
Subscript out of range

I thought because its a multi dimensional array, j was just like i but now I am confused.

Any helps greatly appreciated,
Jeremy.

[Kenneth Hobs]

You get the error because you are using a single dimension array as a multiple dimension array.

If you did want to use a single dimension array, try something like this:
[VBA]Sub test()
Dim productsArray As Variant
Dim countProducts As Integer
Dim i As Single
Dim j As Single
Dim z As Single

'On Error GoTo TheEnd

productsArray = ActiveSheet.UsedRange

countProducts = ActiveSheet.UsedRange.Cells.Count

ReDim resizedproducts(0 To countProducts) ' literally resizes the array? ' which means this is empty!

z = -1
For i = LBound(productsArray, 1) To UBound(productsArray, 1)
For j = LBound(productsArray, 2) To UBound(productsArray, 2)
If productsArray(j, i) <> "" Then
z = z + 1
resizedproducts(z) = productsArray(j, i)
End If
Next j
Next i
ReDim resizedproducts(0 To z)

MsgBox "Ubound(resizedProducts): " & z

TheEnd:
End Sub[/VBA]