My code generates a number based on some inputs and then uses that
number as the Tble(0) value and then is trying to create an array
using that initial value. I do not know how many rows the array will
have, but i don't want it to be bigger then 2550. The next row in the
array is based on the previous row. My problem is the lagged
specification and the incorrect use of the step function. Can anyone
point me in the right direction on this. I will eventually put the
created array in some cells to display it. Thanks a bunch.

Sub Test()
Dim Input1, Input2, Input3, Box As Double
Dim Tble() As String
Dim rw, x As Integer

Input1 = Cells(1, 1).Value
Input2 = Cells(2, 1).Value

Input3 = Input1 * Input2

Cells(3, 1).Value = Input3
rw = 0
ReDim Tble(rw)
Tble(0) = Input3
While Tble(rw) < 2550
For rw = 1 To Step
Tble(rw) = Tble(rw - 1) + 1
rw = rw + 1
Next rw
Wend

End Sub

Sub Test()
Dim Input1 as Double, Input2 as Double
Dim Input3, Box As Double
Dim Tble() As Long
Dim rw as Long , x As Long

Input1 = Cells(1, 1).Value
Input2 = Cells(2, 1).Value

Input3 = Input1 * Input2

Cells(3, 1).Value = Input3
Redim tble(0 to 2550)

Tble(0) = Input3

For rw = 1 To 2550 Step 1
Tble(rw) = Tble(rw - 1) + 1
if rw = 2550 then exit for
Next rw
End Sub

You haven't said how to determine the upper bound, so I used 2550.

--
Regards,
Tom Ogilvy

" wrote:

My code generates a number based on some inputs and then uses that
number as the Tble(0) value and then is trying to create an array
using that initial value. I do not know how many rows the array will
have, but i don't want it to be bigger then 2550. The next row in the
array is based on the previous row. My problem is the lagged
specification and the incorrect use of the step function. Can anyone
point me in the right direction on this. I will eventually put the
created array in some cells to display it. Thanks a bunch.

Sub Test()
Dim Input1, Input2, Input3, Box As Double
Dim Tble() As String
Dim rw, x As Integer

Input1 = Cells(1, 1).Value
Input2 = Cells(2, 1).Value

Input3 = Input1 * Input2

Cells(3, 1).Value = Input3
rw = 0
ReDim Tble(rw)
Tble(0) = Input3
While Tble(rw) < 2550
For rw = 1 To Step
Tble(rw) = Tble(rw - 1) + 1
rw = rw + 1
Next rw
Wend

End Sub

What you are trying to do in your example is not entirely clear. However, if
you know the top bound you are willing to accept, then ReDim your array to
that value and, inside your loop, increment a counter each time you add an
element to the array. AFTER the loop has completed, ReDim the array to that
counter value (possible one less than the counter value depending on where
in the loop you are incrementing it at) BUT make sure you use the Preserve
keyword so as not to lose any data you assigned to the array. A sample
snippet of this would be...

ReDim YourArray(TopBound)
For X = 1 to TopBound
....
.... <<Your code
....
If ExitCondition = True Then Exit Sub
Counter = Counter + 1
Next
ReDim Preserve YourArray(Counter)

For future consideration, if you have a situation where you don't know the
top bound that will result from your loop, use ReDim Preserve in "chunks",
fixing the count at the end. Here is a sample of how to do that.

ChunkAmount = 1000
Do While ExitCondition = False
....
.... <<Your code
....
If Counter = UBound(YourArray) Then
ReDim Preserve YourArray(Counter + ChunkSize)
End If
Counter = Counter + 1
Loop
ReDim Preserve YourArray(Counter -1)

And one final point... this following line of yours

Dim Input1, Input2, Input3, Box As Double

does NOT do what you think it does. Of all the variables listed, only Box
will be a Double... the rest will be Variants. Visual Basic requires each
variable to be declared as to Type individually. So, you could do this...

Dim Input1 As Double, Input2 As Double, Input3 As Double, Box As Double

or list them individually on their own lines (which I think is far more
readable than ganging them up all on one line.

Rick



wrote in message
ups.com...
My code generates a number based on some inputs and then uses that
number as the Tble(0) value and then is trying to create an array
using that initial value. I do not know how many rows the array will
have, but i don't want it to be bigger then 2550. The next row in the
array is based on the previous row. My problem is the lagged
specification and the incorrect use of the step function. Can anyone
point me in the right direction on this. I will eventually put the
created array in some cells to display it. Thanks a bunch.

Sub Test()
Dim Input1, Input2, Input3, Box As Double
Dim Tble() As String
Dim rw, x As Integer

Input1 = Cells(1, 1).Value
Input2 = Cells(2, 1).Value

Input3 = Input1 * Input2

Cells(3, 1).Value = Input3
rw = 0
ReDim Tble(rw)
Tble(0) = Input3
While Tble(rw) < 2550
For rw = 1 To Step
Tble(rw) = Tble(rw - 1) + 1
rw = rw + 1
Next rw
Wend

End Sub

"Rick Rothstein \(MVP - VB\)" wrote...
....
ReDim YourArray(TopBound)
For X = 1 to TopBound
....
.... <<Your code
....
If ExitCondition = True Then Exit Sub
Counter = Counter + 1
Next
ReDim Preserve YourArray(Counter)

Unlike Pascal but like C, VBA preserves the values of For loop
variables after the loop ends. So no need for Counter. Finish with

ReDim Preserve YourArray(X - 1)

Does VB proper work differently?

For future consideration, if you have a situation where you don't
know the top bound that will result from your loop, use ReDim
Preserve in "chunks", . . .

ChunkAmount = 1000
Do While ExitCondition = False
....
.... <<Your code
....
If Counter = UBound(YourArray) Then
ReDim Preserve YourArray(Counter + ChunkSize)
End If
Counter = Counter + 1
Loop
ReDim Preserve YourArray(Counter -1)
....

The main alternative being doubling the array size every time you top
out. The implicit assumption being that, if you don't know the actual
size of your data, best to assume your half way every time you need to
reallocate more storage. The practical justification being that the
2nd time you need to allocate more storage, heuristically it's time to
increase the reallocation chunk size.

Dim a(1 To 100), i As Long
i = 0
Do While whatever
i = i + 1
...
If i = UBound(a) Then ReDim Preserve a(1 To 2 * UBound(a))
Loop
ReDim Preserve a(1 To i)

ReDim YourArray(TopBound)
For X = 1 to TopBound
....
.... <<Your code
....
If ExitCondition = True Then Exit Sub
Counter = Counter + 1
Next
ReDim Preserve YourArray(Counter)

Unlike Pascal but like C, VBA preserves the values of For loop
variables after the loop ends. So no need for Counter. Finish with

ReDim Preserve YourArray(X - 1)

Does VB proper work differently?

No, it works the same in the compiled VB world. My preference for the side
Counter variable is two-fold. First off, "old habits die hard".<g Way back
when, in the early days of VB, there was a concern that Microsoft might
remove the "loop index survives the loop" feature, so it was deemed better
to not get use to it. Second, it makes switching loop structures mid-stream
easier. By that I mean, if you start off with a For-Next loop and decide,
for whatever reason, to convert it to a Do-While loop, the basic
counter-dependent part of the structure, and the code relying on it after
the loop, will pretty much survive the conversion intact.


For future consideration, if you have a situation where you don't
know the top bound that will result from your loop, use ReDim
Preserve in "chunks", . . .

ChunkAmount = 1000
Do While ExitCondition = False
....
.... <<Your code
....
If Counter = UBound(YourArray) Then
ReDim Preserve YourArray(Counter + ChunkSize)
End If
Counter = Counter + 1
Loop
ReDim Preserve YourArray(Counter -1)

The main alternative being doubling the array size every time you top
out. The implicit assumption being that, if you don't know the actual
size of your data, best to assume your half way every time you need to
reallocate more storage. The practical justification being that the
2nd time you need to allocate more storage, heuristically it's time to
increase the reallocation chunk size.

Dim a(1 To 100), i As Long
i = 0
Do While whatever
i = i + 1
...
If i = UBound(a) Then ReDim Preserve a(1 To 2 * UBound(a))
Loop
ReDim Preserve a(1 To i)

I have seen that argument before and do not take issue with it. However, if
you have some familiarity with the data you are processing, you usually can
make a good guess as to an upper limit to use. That should be size you use
in your initial ReDim statement (which I now see I left out of my example).
If you set your chunk size sufficiently large to handle any over flow from a
too-small initial guess, usually you won't have to perform the ReDim more
than once or twice more. Of course, there are open-ended situations where
you don't know how much data is coming at you... in those situations, your
method is definitely the way to go.


Rick