For statement algorithm for VBA


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.

awesomejohn19Asked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
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

Open in new window


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
 
gowflowCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.