• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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.

1 Solution
I don't know does thismeet the requirement ?
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

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.


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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now