# VBA Help - Build Formula based on Variable Count

#### Johnny Thunder

##### Well-known Member
Hello All,

I am working on a project that has several macros but there is one piece of the puzzle I am unable to completed. I will do my best to explain what I need visually.

I have a loop that looks at a sheet (Timing Assumptions) and based on a row called Percentages, the user is able to enter upto 23 variables into the range and I was hoping to systematically build a formula based on the users input. Currently the scenario never gets passed 4 variables but I am hoping to have code that can account for any scenario.

I have mocked up 3 scenarios so you can see how the formulas change based on the user inputs. The end result of the formula will be entered into a cell with the current loop that I have. Just need a way to create the formula.

I am hoping to have a Loop/Join created that will look at the Percentage row and scan the start position of the first used cell and offset(-1,0) and define the number variable above and enter that into the formula within the EDATE(\$J14, Variable) and append to the formula for each percentage in the range.

Any help is much appreciated

Book1
BCDEFGHIJKLMNOPQRSTUVWXYZ
2Media
3Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
4Percentage50%100%
51 Variable
6Formula Result=IFERROR(IF(T\$4=EDATE(\$J14,-1),\$Q14*.50,"")),"")
7
8Media
9Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
10Percentage30%50%10%100%
112 Variable
12Formula Result=IFERROR(IF(T\$4=EDATE(\$J14,-1),\$Q14*.30,IF(T\$4=EDATE(\$J14,0),\$Q14*.50,IF(T\$4=EDATE(\$J14,1),\$Q14*.10,""))),"")
13
14Media
15Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
16Percentage30%50%10%10%100%
174 Variable
18Formula Result=IFERROR(IF(T\$4=EDATE(\$J14,-1),\$Q14*MediaNeg1,IF(T\$4=EDATE(\$J14,0),\$Q14*MediaStart,IF(T\$4=EDATE(\$J14,1),\$Q14*MediaPlus1,IF(T\$4=EDATE(\$J14,2),\$Q14*MediaPlus2,"")))),"")
Sheet1

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Johnny Thunder

##### Well-known Member
Apologies, that last formula result was supposed to look like this:
 IFERROR(IF(T\$4=EDATE(\$J14,-1),\$Q14*.30,IF(T\$4=EDATE(\$J14,0),\$Q14*.50,IF(T\$4=EDATE(\$J14,1),\$Q14*.10,IF(T\$4=EDATE(\$J14,2),\$Q14*.10,"")))),"")

#### Johnny Thunder

##### Well-known Member
Any idea gang?

@Fluff
@Scott T
@DanteAmor
@CSmith
@Joe4
@Norie
@shg
Sorry to tag everyone but you guys have been some of the most helpful people on this site and this is something a bit more technical than the other things I needed help with. Any help is appreciated it.

Bump?

#### CSmith

##### Well-known Member
Assuming I understood your need properly this should get you what you wanting

Place this code in a Module
VBA Code:
Public Function Johnny_Thunder(myVariable As Variant)
Dim acTarget As Range  ' ASSUMING OTHER CELLS ARE ON THE SAME LINE AS THE FORMULA. ADJUST "Set acTarget" LINE AS NEEDED
Dim strFormula As String

Set acTarget = Application.Caller.Offset(0, 0)

Select Case myVariable
Case "1 Variable"
strFormula = "IFERROR(IF(T\$4=EDATE(\$J" & acTarget.Row & ",-1),\$Q" & acTarget.Row & "*.50," & Chr(34) & Chr(34) & "))," & Chr(34) & Chr(34) & ")"
Case "2 Variable"
strFormula = "IFERROR(IF(T\$4=EDATE(\$J" & acTarget.Row & ",-1),\$Q" & acTarget.Row & "*.30,IF(T\$4=EDATE(\$J" & acTarget.Row & ",0),\$Q" & acTarget.Row & "*.50,IF(T\$4=EDATE(\$J" & acTarget.Row & ",1),\$Q" & acTarget.Row & "*.10," & Chr(34) & Chr(34) & ")))," & Chr(34) & Chr(34) & ")"
Case "4 Variable"
strFormula = "IFERROR(IF(T\$4=EDATE(\$J" & acTarget.Row & ",-1),\$Q" & acTarget.Row & "*.30,IF(T\$4=EDATE(\$J" & acTarget.Row & ",0),\$Q" & acTarget.Row & "*.50,IF(T\$4=EDATE(\$J" & acTarget.Row & ",1),\$Q" & acTarget.Row & "*.10,IF(T\$4=EDATE(\$J14,2),\$Q" & acTarget.Row & "*.10," & Chr(34) & Chr(34) & "))))," & Chr(34) & Chr(34) & ")"
Case "Next possibe variable value and so on"
strFormula = "Build out with your needs here for any more."
End Select
If strFormula = "" Then
Johnny_Thunder = 0
Else
Johnny_Thunder = Evaluate(strFormula)
End If
Set acTarget = Nothing
End Function

Here is a sample based on your sheet above:
Johnny_Thunder.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZ
2Media
3Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
4Percentage50%100%
51 Variable
6Formula Result=#VALUE!
7
8Media
9Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
10Percentage30%50%10%100%
112 Variable
12Formula Result=
13
14Media
15Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
16Percentage30%50%10%10%100%
174 Variable
18Formula Result=
Sheet1
Cell Formulas
RangeFormula
C6,C18,C12C6=Johnny_Thunder(B5)

Replies
0
Views
79
Replies
3
Views
185
Replies
4
Views
106
Replies
6
Views
147
Replies
26
Views
687

1,129,730
Messages
5,638,031
Members
417,000
Latest member
JasonWilliam

### 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