Solved: How do you set up this simple array?

[doctortt]

My codes will calculate A, B, C, and D in a loop, and the loop will stop at certain point.

After the calculation in *each* loop, how do I store each value to an array?

I believe this is a dynamic array because the loop will run for X times depending on the user entries for the calculation.

[Simon Lloyd]

Can you supply the code?, what do you want to do with the results afterwards?, how do you want them displayed?

[doctortt]

Can you supply the code?, what do you want to do with the results afterwards?, how do you want them displayed?

Basically, I want the array to store all those values. I will have a radio button. If user clicks yes, Excel will spit out the data in the array to a new sheet, otherwise, I will do nothing.

A,B,C,D are just variables from the equation. Can I not post the codes?

[Simon Lloyd]

You state "My codes will calculate A, B, C, and D in a loop, and the loop will stop at certain point" please post the code you are using to do this then we can help further.

[doctortt]

Codes are like this.

B = range("blah1")
C = range("blah2")

Do

A = an equatioN

B = B + 1
C = C + 1
Loop Until A > 123456789

I would like to setup an array to store the value of A, B, C in each loop.

Then if the user selects a radio button on the worksheet. Excel will spit out the stored data in the array to a new work sheet.

[Simon Lloyd]

If you truly want help post your code, it's not a secret, whatever you think you have created thousands of people have done it and probably better, so post it and get help :)

[doctortt]

Here you go. I'm stuck. I want to build an array to store A, B, and C into the memory.

A = .20 * 50000
B = Worksheet("test1").range("A1")
c = 1
Do
A = A * .04 + A + 50000 * .20 * (1.1)^c
c = c + 1
B = B + 1
Loop Until A > 100000

[Simon Lloyd]

try this:
Dim i As Long, MyArrayA, MyArrayB, MyArrayC
A = 0.2 * 50000
B = Worksheet("test1").Range("A1")
C = 1
i = 1
Do
A = A * 0.04 + A + 50000 * 0.2 * (1.1) ^ C
C = C + 1
B = B + 1
Let MyArrayA(i) = A
Let MyArrayB(i) = B
Let MyArrayC(i) = C
i = i + 1
Loop Until A > 100000

[doctortt]

Doesn't work

Run-time error '13':
Type Mismatch

[Simon Lloyd]

Its really early hours here, an array needs to know how many element will be in it at runtime, so given the calculation you gave me there will be 6 elements to reach over 100000 so this should workDim i As Double, MyArrayA() As Long, MyArrayB() As Long, MyArrayC() As Long
A = 0.2 * 50000
B = Sheets("Sheet1").Range("A1").Value
C = 1
ReDim MyArrayA(6)
ReDim MyArrayB(6)
ReDim MyArrayC(6)
For i = 1 To 6
A = A * 0.04 + A + 50000 * 0.2 * (1.1) ^ C
C = C + 1
B = B + 1
MyArrayA(i) = A
MyArrayB(i) = B
MyArrayC(i) = C
Next i

[Kenneth Hobs]

Option Explicit
Option Base 1

Sub abc()
Dim A As Double, B As Double, C As Double, abc() As Double
Dim i As Long

i = 0
A = 0.2 * 50000
B = Worksheets("test1").Range("A1")
C = 1

Do
A = A * 0.04 + A + 50000 * 0.2 * (1.1) ^ C
i = i + 1
C = C + 1
B = B + 1
ReDim abc(1 To 3, 1 To i)
abc(1, i) = A
abc(2, i) = B
abc(3, i) = C
Loop Until A > 100000

MsgBox "Last A: " & A & vbLf & _
"Last B: " & B & vbLf & _
"Last B: " & B

MsgBox "Last Value abc(1, " & i & "): " & abc(1, i) & vbLf & _
"Last Value abc(2, " & i & "): " & abc(2, i) & vbLf & _
"Last Value abc(3, " & i & "): " & abc(3, i)
End Sub

[mikerickson]

If you don't know how many elements there will be in the array, something like this

Sub test()
Dim A As Double, B As Double, C As Double

Dim aColl As New Collection, aArray As Variant
Dim bColl As New Collection, bArray As Variant
Dim cColl As New Collection, cArray As Variant

A = 0.2 * 50000
B = Worksheets(1).Range("A1")
C = 1
Do
A = A * 0.04 + A + 50000 * 0.2 * (1.1) ^ C
C = C + 1
B = B + 1
aColl.Add Item:=A, Key:=CStr(B)
bColl.Add Item:=B, Key:=CStr(B)
cColl.Add Item:=C, Key:=CStr(B)
Loop Until A > 100000

ReDim aArray(1 To B)
ReDim bArray(1 To B)
ReDim cArray(1 To B)

For i = 1 To B
aArray(i) = aColl(i)
bArray(i) = bColl(i)
cArray(i) = cColl(i)
Next i

End Sub

[doctortt]

thanks all.