VBA Help - Need to modify code to build a formula x amount of times

[Johnny Thunder]

Hello all,

I am working on creating formulas for a summary sheet and found a link to code that build permuations and its on the right track but I am not saavy enough to modify it.

Here is the explanation.

I have 2 columns, Column G is a for Prior Period, Column I is for Current. There is only 14 rows of data.... so a pretty small data set. Where this gets complicated, is I have a matrix to the right of each row and in the matrix is account numbers column P:AI (Exactly 20 Possibly GL Account numbers)

Most of these fields are left blank but certain rows have accounts populated.

I need a way to build a formula permutation that essentially does a SumIF in the cell and for each account number It adds one more SUMIF with a + in between each formula.

So here is an example:

For Row 11 - Column P contains "15660010" And Column Q "16200000"

So in Column G (Prior Period) the formula should look like this = "=SUMIF(GItem, P15, CItem)+SUMIF(GItem, Q15, CItem)"
Explanation: GItem equals my Named Range 0f GL Accounts on my data source Sheet, P15 equals "15660010", CItem equals the totals I need.

So the more GL Accounts there are in the matrix then the longer the formula gets. If there is no GL Account in the field skip over it.

Any Help is appreciated.

Here is what I found on the web, This will build a permutation with the string "Dog" and it populates the data in Column A. I am not sure how to modify this code to fit my requirements.

Dim CurrentRow
Sub GetString()


Dim InString As String
Dim FormulaR As String


InString = "Dog"


ActiveSheet.Columns(1).Clear
    CurrentRow = 1
        Call GetPermutation("", InString)
    
End Sub


Sub GetPermutation(x As String, y As String)


Dim i As Integer, j As Integer
    
    j = Len(y)
    If j < 2 Then
        Cells(CurrentRow, 1) = x & y
        CurrentRow = CurrentRow + 1
    Else
        For i = 1 To j
            
            Call GetPermutation(x + Mid(y, i, 2), Left(y, i - 1) + Right(y, j - i))
        Next
    
    End If


End Sub