VBA Help - Creating Multiple Variations of the same formula based on a List

[Johnny Thunder]

Problem: I have a Matrix of G/L accounts & Cost Center Variables. There is a possibility of 20 Cost Centers and 20 G/L Account Combinations for each of my Categories.

1. For each category, there are a 20 Possible Cost Centers as well as 20 G/L Account variations.
2. I need a way to dynamically loop thru each category and if a value exist on both the Cost Center row as well as the G/L Account Row, to apply a SUMIF Formula and retain the result in a scripting dictionary or on a blank sheet for entry once all possible variations have been found, then on a summary sheet, drop in the value.
3. So as an example: For Category “Publicity” There is only one Cost center with Two G/L Account possibilities.
1^stPossible comination: =INDEX('GL x CC Q2 Proj NA'!$E$12:$AO$943,MATCH(“7310000”,'GL x CC Q2 Proj NA'!$A$12:$A$943,0),MATCH(“1920”,'GL x CC Q2 Proj NA'!$E$10:$AO$10,0))/1000
· 7310000 = G/Account
· 1920 = Cost Center
2^ndPossible Combination: =INDEX('GL x CC Q2 Proj NA'!$E$12:$AO$943,MATCH(“7860000”,'GL x CC Q2 Proj NA'!$A$12:$A$943,0),MATCH(“1920”,'GL x CC Q2 Proj NA'!$E$10:$AO$10,0))/1000
· 7860000 = G/L Account
· 1920 = Cost Center
4. So how I would expect this to work is that the code will start on Sheet Definitions
a. Start at Cell A2 (Column for grabbing the Cateogry Title
b. Once a Category has been copied/found then shift to Staging Sheet and enter the first formula variation in Cell A2, second variations in cell B2 and continue writing all the variation formulas based on how many have been entered into the matrix.
c. Once all variations for the first grouping have completed, step 2 rows down, enter the category name and then start building the formulas

Here is the code I have so far that is able to do the above but for only 1 variation. You can see where I have hardcoded cell references where I am not sure how to make it more dynamic and to have the code loop in those areas to create all the formulas.

Any help is appreciated!

Sub SecretSauce()


Dim ws1          As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim rng           As Range, cell As Range
Dim LastR        As Long
Dim cCount     As String, gCount As String, Class As String, cItem As String, gItem As String
Dim x              As Long, y As Long


Set ws1 = Sheets(8) 'Sum Of ops 2
Set ws2 = Sheets("Definitions")
Set ws3 = Sheets("Staging")


Set rng = ws2.Range("A2:A144")


    For Each cell In rng
        If cell <> vbNullString Then    'Will only fire if Cell has data
            
             Class = cell.Offset(0, 4).Value 'Name of Grouping
             cCount = cell.Offset(1, 3).Value 'Defines How many combinations of Cost Centers are needed
             gCount = cell.Offset(2, 3).Value 'Defines how many combinations of G/L Accounts are needed
             
             cItem = cell.Offset(1, 5).Value 'Defines the Cost Center -------------------- 'Currently set to a specific Cell, needs to be dynamic based on cCount variable
             gItem = cell.Offset(2, 5).Value 'Defines the G/L Account --------------------'Currently set to a specific Cell, needs to be dynamic based on gCount variable
            
                'Cost Center Definition
                If cItem = "All" Then
                    cItem = "SVOC"   'SVOC is the equivelant of all Cost Centers, essentially giving a summary
                 End If
                 
                'G/L Account Definition
                 If gItem = "All" Then
                    gItem = "Total Department Spend"   'Total Department Spend is the equivelant of all G/L Accounts, essentially giving a summary
                 End If
            
            With ws3 'Enter Formula Results on Sheet Staging
            If cCount <> 0 And gCount <> 0 Then
                ActiveCell.Value = Class
                ActiveCell.Offset(1, 0).Formula = "=INDEX(DataTable,MATCH(""" & gItem & """,GLData,0),MATCH(""" & cItem & """,Hdata,0))/1000"  'Data Table = All Data in Table, GLData = G/L Account Lookup Column,  HData = Headers on Report-------------Need to have the code move on column to right after each variation of Cost Center/Gl Account
                ActiveCell.Offset(3, 0).Select 'Steps down for the next Grouping on Staging sheet
            Else
                'Do Nothing
            End If
            End With
    
        End If
    Next cell
        


End Sub