VBA Help - Creating a Formula with x amount of variables based on Counter

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
673
Office Version
  1. 2016
Platform
  1. MacOS
Hello All,

I have a range that has a counter in it that goes from -11 to + 11 and some percentages below. This then feeds into a formula on another sheet that currently is manually created. I am trying to have a script that will scan if a percentage has been entered and based on the value above it, update the formula with however many variables are needed.

Example:
Media Scripted & Kids
Periods-11-10-9-8-7-6-5-4-3-2-101234567891011Totals
Percentage30%50%10%10%100%


The formula for this line looks like this: =IFERROR(IF(AS$4=EDATE($J14,-1),$Q14*MediaNeg1,IF(AS$4=EDATE($J14,0),$Q14*MediaStart,IF(AS$4=EDATE($J14,1),$Q14*MediaPlus1,IF(AS$4=EDATE($J14,2),$Q14*MediaPlus2,"")))),"")

Explanation:
1. AS$4 is a Date, Nov-19.
2. J14 = Nov-19
3. EDATE($J14,-1) is a statement that takes a Date from J14 -1 Month (Oct-19)
4. Q14 * MediaNeg1 (Q14 is a Dollar Amount $1000), MediaNeg1 is a Named Range to the table above looking at the -1 percentage which is 30%. Because this was the first variable in the table from above that is why it was entered first.
4a. The EDATE Part of the formula is needed because it is taking a Date and applying some +1 Month or -1 Month to the value.
5. IF(AS$4=$J14,$Q14*MediaStart - Is the same as the above using the next variable from the Table above in position 0 which is the 50%. I think if I am able to do this with VBA, I would just put the percentages instead of the Named ranges like MediaStart.
6. Exact same as above just the next variable from the table.

Hopefully you can see what the formula is doing now. What I need is a way to systematically build the formula from the first variable in the table to the last which just appends to the formula based on how many Percentages are entered into the table.

Here is a mock up with the above using different ranges so you can see how the formula works and looks when completed.
Marketing Model - Proposal Working File v2.5.xlsm
GHIJKLMNOPQRSTU
1Name of ProjectIdea DateProductionCompletedAmtJul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20
2Name 1Nov-18May-19Nov-19 $ 3,000    9001500300300  
Sheet1
Cell Formulas
RangeFormula
M2:U2M2=IFERROR(IF(M$1=EDATE($J2,-1),$K2*MediaNeg1,IF(M$1=EDATE($J2,0),$K2*MediaStart,IF(M$1=EDATE($J2,1),$K2*MediaPlus1,IF(M$1=EDATE($J2,2),$K2*MediaPlus2,"")))),"")
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,118,342
Messages
5,571,657
Members
412,412
Latest member
NWPhotoExplorer
Top