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

#### Johnny Thunder

##### Well-known Member
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.

Replies
1
Views
430
Replies
0
Views
594

### Forum statistics

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.

### Which adblocker are you using?    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

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