Changing Columns in VBA across multiple formulas based on date

user_19839983 0
Viewed 26

I have the below generated from the Macro Recorder. I want to update the column reference in several formulas, however when I started with the first one it keeps erroring out. I'm obtaining the current Month with the input box. I need to subtract 4 from the C below resulting in -1, then add the value of Mon_Num. Resulting in the correct column reference.

Can anyone help me make this dynamic so that I can update the remaining formulas in my series?

Dim Prior_Month As Date Dim Mon_Num As Integer

Prior_Month = Application.InputBox("Please input last day of Fiscal Period", "Period End", "Enter Date")

Mon_Num = Month(Prior_Month)

Range("F6:F7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+FORECAST!R[79]C[3]/1000"
vba excel
1 Answers

If I understand correctly, you need to add the value in Mon_num to negative one (-1) and integrate that as the column value in the formula. If so, just change the last line of your code to be as follows:

ActiveCell.FormulaR1C1 = "=+FORECAST!R[79]C[" & Mon_num - 1 & "]/1000"

If I have misunderstood what you are trying to accomplish, please clarify in a comment below.

user_22206650
Related