Dynamic array

[rafaeltini]

i'm starting vba, and i'm having problem with this code
i wanted that it automatically calcule the size of the array, based on
the condition "Quente", then it should be copying one matrix 2xN ,
and moving to one dimensional array, so that i can output the resul in
ascending order on the colunn j

can anyone help me? i don't know what i'm doing wrong here

Public Sub org()

Dim tempVar As Integer
Dim anotherIteration As Boolean
Dim I As Integer
Dim arraySize As Integer
Dim myArray(), myArray2(), quente(), frio(), frio2() As Integer
''-----------
''Get the array size.
''-----------
I = 3

If Cells(I, "E").Value = "Quente" Then

Do
arraySize = J
arraySize = arraySize + 1
I = I + 1
J = J + 1
Loop Until Cells(I, "E").Value = ""
End If

ReDim myArray(arraySize - 1)
''---------------------
''Get the values. Convert text to numbers.
''---------------------
For I = 0 To arraySize

If Cells(I, "E").Value = "Quente" Then
For J = 0 To arraySize
myArray(I) = Val(Cells(3 + J, "B").Value)
myArray(I + 1) = Val(Cells(3 + J, "c").Value)
I = I + 2
J = J + 1
Next J

End If

Next I
Do
anotherIteration = False
For I = 0 To arraySize - 2
If myArray(I) > myArray(I + 1) Then
tempVar = myArray(I)
myArray(I) = myArray(I + 1)
myArray(I + 1) = tempVar
anotherIteration = True
End If
Next I

Loop While anotherIteration = True
''------------
''Write data to column J.
''------------
For I = 3 To arraySize
Cells(I, "j").Value = myArray(I - 1)
Next I
End Sub

 

[Bob Phillips]

This might get you started on loading the array

I = 3

If Cells(I, "E").Value = "Quente" Then

ReDim myArray(0 To 1, 0 To 0)
Do
I = I + 1
If Cells(I, "E").Value <> "" Then
ReDim Preserve myArray(0 To 1, 0 To arraySize)
myArray(0, arraySize) = Cells(I, "B").Value
myArray(1, arraySize) = Cells(I, "C").Value
arraySize = arraySize + 1
End If
Loop Until Cells(I, "E").Value = ""
End If


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)