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
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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.

Please try this code and advise:
'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

Open in new window


Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Dave,

That works!

Much thanks,

B.
0
dlmilleCommented:
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

Open in new window


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

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.