Bright01
asked on
Enhancement to Macro for Driving Dynamic Time Horizon
I have a great macro/WS that DMille wrote for me that provides for a Dynamic Time Horizon. The attached model has the ability to add new "Use Cases" and when it does, the Time Horizon capability does not work correctly. When the rows are replicated (i.e. click the + User Case button), and then you change the Time Horizons, the new User Cases do not respond accordingly. The original one does but not the new ones. I think it is because it is "hard coded" into the macro. I think it's in these two reference points:
Set rngClear = Range("AJ2", Cells(4, Columns.Count)).Resize(6)
rngClear.ClearContents
numPeriods = Range("AB3")
Thank you in advance.....
B.
Workbench-v26.xlsm
Set rngClear = Range("AJ2", Cells(4, Columns.Count)).Resize(6)
rngClear.ClearContents
numPeriods = Range("AB3")
Thank you in advance.....
B.
Workbench-v26.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This post is more clever and uses the latest post from the prior question which for some reason you weren't.
Dave
'New code to expand months
Private Sub CheckExpMonths(ByVal Target As Range)
Dim numPeriods As Long
Dim periodDisplay As Long
Dim rngClear As Range
Dim LastCol As Long
If Not Intersect(Target, Range("AB:AB")) Is Nothing Then
Application.EnableEvents = False
LastCol = Cells(4, Columns.Count).End(xlToLeft).Column
Range("AJ2", Cells(2, Columns.Count)).ClearContents
Set rngClear = Range("AJ" & Target.Row - IIf(Target.Row = 3, 1, 0), Cells(Target.Row, Columns.Count)).Resize(3)
rngClear.ClearContents
numPeriods = Range("AB" & Target.Row).Value
periodDisplay = Evaluate("=MAX(AB:AB)") 'get max periods to display
If numPeriods >= 1 Then
With Range("AI2")
.Resize(, periodDisplay).DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlMonth, Step:=1, Trend:=False
End With
Range("AI" & Target.Row & ":AI" & Target.Row + 1).Copy Range("AI" & Target.Row & ":AI" & Target.Row + 1).Resize(, numPeriods)
End If
Application.EnableEvents = True
End If
End Sub
Dave
Bright - you need to use the latest post, above. After more testing more tweaks were necessary.
Dave
Dave
ASKER
That works!
Much thanks,
B.