Solved

For statement algorithm for VBA

Posted on 2011-09-24
224 Views

c array size is  x
b array size is x2

I have the following table for example.

c(1) = b(1)
c(2)=((b(2)+1)*(c(1)+1))-1      c(6)=b(2)
c(3)=)(b(3)+1)*(c(2)+1))-1      c(7)=((b(3)+1)*c(6)+1))-1      c(10)=b(3)
c(4)=)(b(4)+1)*(c(3)+1))-1      c(8)=((b(4)+1)*c(7)+1))-1      c(11)=((b(4)+1)*c(10)+1))-1
c(5)=)(b(5)+1)*(c(4)+1))-1      c(9)=((b(5)+1)*c(8)+1))-1      c(12)=((b(5)+1)*c(11)+1))-1

How would i put these in a for statement? Again C goes until x, B goes until x2.

0
Question by:awesomejohn19

LVL 29

Expert Comment

I don't know does thismeet the requirement ?
gowflow
``````Sub ForStatement()

Dim I As Long, J As Long, X As Long
Dim B(X), C(2 * X)

For I = 1 To X
For J = 0 To 4
C(I) = (B(I) + 1) * (C(J) + 1) - 1
Next J
Next I

End Sub
``````
0

LVL 41

Accepted Solution

dlmille earned 500 total points
Here's your solution - note I've initialized x2 as a constant = 5 per your example, which can be changed based on how you're using this in your algorithm.  x must be some factor larger than x2, example if x2 = 5, x must be 12, if x2 = 10, x must be  52, etc.  As a result, I redimensioned c based on that fact

``````Const x2 = 5
'Const x = 12 'must be large enough to get through all the iterations... e.g., if x2 = 5, then x must be 12, if x2 = 10 then x must be 52, etc.
'as array c() is a function of array b(), it stand to reason that c() can be dynamic and allocate what it needs on the fly, thus the declaration,
'and the redim preserve statement
Sub forStmt()

Dim i As Long, j As Long, k As Long
Dim b(1 To x2) As Variant, c() As Variant

k = 1
For i = 1 To x2 - 2
For j = i To x2
ReDim Preserve c(k) As Variant

c(k) = IIf(j = i, b(j), (b(j) + 1) * (c(k - 1) + 1) - 1)

k = k + 1

Next j
Next i

End Sub
``````

For fun, see attached workbook - enter any value for x2 > 3 and you'll see your table of formulas.  This demonstrates that what I have given you works exactly as you've specified.

Cheers,

Dave
genFormula-r1.xlsm
0

Featured Post

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.