We help IT Professionals succeed at work.

# Enhancement to Macro for Driving Dynamic Time Horizon

on
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")

B.
Workbench-v26.xlsm
Comment
Watch Question

## View Solution Only

Most Valuable Expert 2012
Top Expert 2012
Commented:
I think I've sorted the problem, as the calculations need to be based on the Target row, while also keeping the timespan in row 2 expanded to the max needed based on any entry in AB.

``````'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

Set rngClear = Range("AJ" & Target.Row - IIf(Target.Row = 3, 1, 0), Cells(Target.Row, Columns.Count)).Resize(6)

rngClear.ClearContents

numPeriods = Range("AB" & Target.Row)

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
End If

Range("AI" & Target.Row & ":AI" & Target.Row + 1).Copy Range("AI" & Target.Row & ":AI" & Target.Row + 1).Resize(, numPeriods)

Application.EnableEvents = True
End If

End Sub
``````

Dave

Commented:
Dave,

That works!

Much thanks,

B.
Most Valuable Expert 2012
Top Expert 2012

Commented:
This post is more clever and uses the latest post from the prior question which for some reason you weren't.
``````'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
Most Valuable Expert 2012
Top Expert 2012

Commented:
Bright - you need to use the latest post, above.  After more testing more tweaks were necessary.

Dave