VBA Help - Build Formula based on Variable Count

[Johnny Thunder]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	Media
3	Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
4	Percentage												50%												100%
5	1 Variable
6	Formula Result=	IFERROR(IF(T$4=EDATE($J14,-1),$Q14*.50,"")),"")
7
8	Media
9	Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
10	Percentage											30%	50%	10%											100%
11	2 Variable
12	Formula 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
14	Media
15	Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
16	Percentage											30%	50%	10%	10%										100%
17	4 Variable
18	Formula 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

 

[Johnny Thunder]

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]

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.

 

[Johnny Thunder]

Bump?

 

[CSmith]

Assuming I understood your need properly this should get you what you wanting

Place this code in a Module

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	Media
3	Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
4	Percentage												50%												100%
5	1 Variable
6	Formula Result=	#VALUE!
7
8	Media
9	Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
10	Percentage											30%	50%	10%											100%
11	2 Variable
12	Formula Result=
13
14	Media
15	Periods	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	Totals
16	Percentage											30%	50%	10%	10%										100%
17	4 Variable
18	Formula Result=
Sheet1

Cell Formulas
Range	Formula
C6,C18,C12	C6	=Johnny_Thunder(B5)