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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
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.
1stPossible 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
2ndPossible 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!

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,129,903
Messages
5,638,899
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top