Excel Date Header Macro - A ShareGPT conversation

Model: GPT-4

I'd like to create a macro in Excel that creates a series of dates (i.e. a date header). The dates are either one month, one quarter, or one year apart. Each date represents the last day in a given financial period. The user experience should be as follows: First, the user tells the macro in which cell the 'Analysis Start Date' appears. Using that 'Analysis Start Date' as a reference, the macro then calculates the first date in the date header. The first date in the series should be calculated as follows: =EOMONTH('Analysis Start Date' cell reference, -1). Thus, the first date in the date header is the last day of the month in the month prior to the Analysis Start Date month. Second, the user tells the macro whether periods should be Months, Quarters, or Years. The user would enter M for Month, Q for Quarter, or Y for Year. Third, the user tells the macro how many years are in the analysis period. Thus, if there are 10 years in the analysis period and the user chose monthly periods, the macro would output 120 dates (10 x 12), each one month apart. Make sure to use the EOMONTH function to calculate each of the dates. If the user selects 5 years in the analysis period, and the user chose quarterly periods, the macro would output 20 dates (5 x 4), each one quarter apart. Finally, format the cells in the date header with a MM/DD/YYYY formatting.

You can create a macro in Excel using VBA (Visual Basic for Applications) to achieve this. Follow these steps to create the macro:

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
Click on Insert and then select Module to create a new module.
Copy and paste the following VBA code into the module:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As String
    
    cellRef = InputBox("Enter the cell reference containing the Analysis Start Date (e.g., A1):")
    startDate = Application.WorksheetFunction.EoMonth(Range(cellRef), -1)
    
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    Dim i As Integer
    Dim curDate As Date
    curDate = startDate
    
    For i = 1 To numPeriods
        ws.Cells(1, i + 1).NumberFormat = "MM/DD/YYYY"
        ws.Cells(1, i + 1).Value = curDate
        
        Select Case UCase(periodType)
            Case "M"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 1)
            Case "Q"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 3)
            Case "Y"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 12)
        End Select
    Next i

End Sub

Close the VBA editor by clicking the X in the top-right corner or pressing Alt + Q.
Press Alt + F8 to open the "Macro" dialog box. You should see GenerateDateHeader in the list of macros. Select it and click "Run" to execute the macro.

Now, the macro will ask you for the required information, like the cell reference containing the Analysis Start Date, the period type (M, Q, or Y), and the number of years in the analysis period. It will then generate the date header based on the provided information and format the cells with the MM/DD/YYYY format.

Let's modify this code: cellRef = InputBox("Enter the cell reference containing the Analysis Start Date (e.g., A1):") startDate = Application.WorksheetFunction.EoMonth(Range(cellRef), -1) Instead of the user manually typing in the cell reference, have the user choose the cell referencing by clicking the cell reference.

To modify the code so that the user can click on the cell reference instead of typing it, you can use the Application.InputBox function with the Type argument set to 8. This allows the user to select a cell from the worksheet. Here's the modified code:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub

One other change to the code. Rather than automatically starting the date header in cell B1, ask the user to choose in which cell the date header should begin. Likewise have the user select the cell by clicking the cell rather than typing it in.

You can modify the code to allow the user to click on the cell where the date header should begin. Here's the updated code:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    Dim i As Integer
    Dim curDate As Date
    curDate = startDate
    
    For i = 1 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        dateHeaderStart.Offset(0, i - 1).Value = curDate
        
        Select Case UCase(periodType)
            Case "M"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 1)
            Case "Q"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 3)
            Case "Y"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 12)
        End Select
    Next i

End Sub

With this modification, the user is asked to select a cell for the date header to start. The macro then generates the date header, beginning from the selected cell.

Great. Now let's improve the macro further. Immediately below the Date Header row, I'd like to create a row that is the Month, Quarter, or Year row. This row outputs a numeric value that represents which month, quarter, or year the date above it represents. So, for instance, if the user selects M with 120 periods, the second row would start with 1 and go to 120.

You can modify the macro to generate a row with numeric values representing the month, quarter, or year depending on the selected period type. Here's the updated code:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    Dim i As Integer
    Dim curDate As Date
    curDate = startDate
    
    For i = 1 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        dateHeaderStart.Offset(0, i - 1).Value = curDate
        dateHeaderStart.Offset(1, i - 1).Value = i
        
        Select Case UCase(periodType)
            Case "M"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 1)
            Case "Q"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 3)
            Case "Y"
                curDate = Application.WorksheetFunction.EoMonth(curDate, 12)
        End Select
    Next i

End Sub

This modification adds a row below the date header row that contains numeric values representing the month, quarter, or year based on the user's selection. If the user selects "M" with 120 periods, the second row will contain numbers from 1 to 120.

That works great, but rather than hard coded values I'd prefer formulas in the cells. So, for instance, the first cell should have a formula that is =EOMONTH([Analysis Start Date cell reference],-1), the second cell should have a formula that is =EOMONTH(previous date, 1), and that second formula would be copied out. Likewise, the second row would have a value of 1 in the first cell, and than a formula of =previous cell + 1 and copied out.

You can modify the macro to use formulas for both the date header row and the second row with numeric values. Here's the updated code:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    Dim i As Integer
    dateHeaderStart.NumberFormat = "MM/DD/YYYY"
    dateHeaderStart.Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
    dateHeaderStart.Offset(1, 0).Value = 1
    
    For i = 2 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        
        Select Case UCase(periodType)
            Case "M"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 1)"
            Case "Q"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 3)"
            Case "Y"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 12)"
        End Select
        
        dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset(1, i - 2).Address & " + 1"
    Next i

End Sub

This modification adds formulas to the date header row based on the selected period type and the Analysis Start Date cell reference. The second row now contains the value of 1 in the first cell, followed by formulas that increment the value by 1.

Working great. In finance, the first period is called period zero. It represents everything that happened up until that point. As a result, we need to make the following changes: In row one, the macro should output one additional period beyond what the user selects. So, if the user chooses 10 years with monthly periods (i.e. 10 x 12), there should be 121 periods (10 x 12 + 1). If the user selects 5 years, with quarterly periods (5 x 4), there should be 21 periods ( 5 x 4 + 1). In row two, the first value should be 0, with each value being = previous + 1.

You can modify the macro to include period zero and output one additional period based on the user's input. Here's the updated code:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12 + 1
        Case "Q"
            numPeriods = analysisPeriod * 4 + 1
        Case "Y"
            numPeriods = analysisPeriod + 1
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    Dim i As Integer
    dateHeaderStart.NumberFormat = "MM/DD/YYYY"
    dateHeaderStart.Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
    dateHeaderStart.Offset(1, 0).Value = 0
    
    For i = 2 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        
        Select Case UCase(periodType)
            Case "M"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 1)"
            Case "Q"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 3)"
            Case "Y"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 12)"
        End Select
        
        dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset(1, i - 2).Address & " + 1"
    Next i

End Sub

With this modification, the macro now outputs one additional period and starts the numeric values in the second row with 0. The first period is considered period zero, and the rest of the periods increment by 1.

Looks like you made an error. In testing, I chose monthly periods and 10 years, and it returned 1441 periods. Please let me know which part of the code to change to fix the issue. No need to give me the entire code over again, just the part to fix.

Apologies for the confusion. It looks like the code is working correctly for quarters and years, but not for months. The issue lies in the calculation of numPeriods. Please change the following lines:

vbaCopy code
Select Case UCase(periodType)
    Case "M"
        numPeriods = analysisPeriod * 12 + 1
    Case "Q"
        numPeriods = analysisPeriod * 4 + 1
    Case "Y"
        numPeriods = analysisPeriod + 1
    Case Else
        MsgBox "Invalid period type entered. Please use M, Q, or Y."
        Exit Sub
End Select

to:

vbaCopy code
Select Case UCase(periodType)
    Case "M"
        numPeriods = analysisPeriod * 12
    Case "Q"
        numPeriods = analysisPeriod * 4
    Case "Y"
        numPeriods = analysisPeriod
    Case Else
        MsgBox "Invalid period type entered. Please use M, Q, or Y."
        Exit Sub
End Select

numPeriods = numPeriods + 1

This change separates the addition of 1 from the calculation of the number of periods for each period type, ensuring that the correct number of periods is calculated for each case.

Excellent! Let's finish by adding an (optional) third row. This third row should only appear when the user chooses M or Q, and is calculated by rounding up with no decimal places the value in the second row divided by either 12 for M or 4 for Q. Or in other words, add a formula in the third row for each period that is =ROUNDUP(period value/12 in the case of M or period value/4 in the case of Q,0). Additionally, please add notes within the code so that others who view the code know what each section does.

Here's the updated macro with an optional third row, including comments explaining each section:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    ' Define the necessary variables
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    ' Get the cell containing the Analysis Start Date
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    ' Get the period type and the number of years in the analysis period
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    ' Get the cell where the date header should begin
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    ' Calculate the number of periods
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    numPeriods = numPeriods + 1
    
    ' Generate the date header and numeric value rows
    Dim i As Integer
    dateHeaderStart.NumberFormat = "MM/DD/YYYY"
    dateHeaderStart.Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
    dateHeaderStart.Offset(1, 0).Value = 0
    
    For i = 2 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        
        Select Case UCase(periodType)
            Case "M"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 1)"
            Case "Q"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 3)"
            Case "Y"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 12)"
        End Select
        
        dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset(1, i - 2).Address & " + 1"
        
        ' Add the optional third row for monthly or quarterly periods
        If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
            If UCase(periodType) = "M" Then
                dateHeaderStart.Offset

Seems you stopped. Please continue the code starting from: If UCase(periodType) = "M" Then dateHeaderStart.Offset

Apologies for the truncation. Continuing the code from where it was cut off:

vbaCopy code
        ' Add the optional third row for monthly or quarterly periods
        If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
            If UCase(periodType) = "M" Then
                dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/12, 0)"
            ElseIf UCase(periodType) = "Q" Then
                dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/4, 0)"
            End If
        End If
    Next i

End Sub

This code adds the optional third row with the respective formulas when the user chooses either monthly or quarterly periods.

Let's edit the Third Row logic. Please suggest the code to change in the 'Add the optional third row section such that the third row starts in period zero. Or in other words, you currently have the third row starting as of period 1 rather than as of period 0. No need to return the entire code, just the edits to the third row section.

To make the third row start at period zero, you can simply move the third-row logic outside of the loop for periods 1 and onwards. The change to the 'Add the optional third row section' would be:

vbaCopy code
' Add the optional third row for monthly or quarterly periods
If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
    dateHeaderStart.Offset(2, 0).Value = 0
    
    For i = 2 To numPeriods
        If UCase(periodType) = "M" Then
            dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/12, 0)"
        ElseIf UCase(periodType) = "Q" Then
            dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/4, 0)"
        End If
    Next i
End If

With these changes, the third row will start at period zero when the user chooses either monthly or quarterly periods.

Hmmm. Not sure where to paste that.

Apologies for the confusion. Please find the complete updated code below with comments. The updated 'Add the optional third row' section has been integrated appropriately:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    ' Define the necessary variables
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    ' Get the cell containing the Analysis Start Date
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    ' Get the period type and the number of years in the analysis period
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    ' Get the cell where the date header should begin
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    ' Calculate the number of periods
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    numPeriods = numPeriods + 1
    
    ' Generate the date header and numeric value rows
    Dim i As Integer
    dateHeaderStart.NumberFormat = "MM/DD/YYYY"
    dateHeaderStart.Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
    dateHeaderStart.Offset(1, 0).Value = 0
    
    ' Add the optional third row for monthly or quarterly periods
    If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
        dateHeaderStart.Offset(2, 0).Value = 0
    End If
    
    For i = 2 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        
        Select Case UCase(periodType)
            Case "M"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 1)"
            Case "Q"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 3)"
            Case "Y"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 12)"
        End Select
        
        dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset

Looks like you stopped. Please continue from: dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset

Apologies for the truncation. Continuing the code from where it was cut off:

vbaCopy code
        dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset(1, i - 2).Address & " + 1"
        
        ' Add the optional third row for monthly or quarterly periods
        If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
            If UCase(periodType) = "M" Then
                dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/12, 0)"
            ElseIf UCase(periodType) = "Q" Then
                dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/4, 0)"
            End If
        End If
    Next i

End Sub

This is the complete code, including the updated 'Add the optional third row' section. The third row will start at period zero when the user chooses either monthly or quarterly periods.

Excellent. One final feature I'd like added. The rows currently don't have labels to help the user identify what each row means. Please include in the first cell for each row the following labels (right-aligned): Period Ending Analysis Month Analysis Year

To add labels to the first cell of each row, you can simply set the cell value for the cells to the left of the starting cells. Here's the updated code:

vbaCopy code
Option Explicit

Sub GenerateDateHeader()

    ' Define the necessary variables
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim startDate As Date
    Dim analysisPeriod As Integer
    Dim periodType As String
    Dim cellRef As Range
    Dim dateHeaderStart As Range
    
    ' Get the cell containing the Analysis Start Date
    On Error Resume Next
    Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
    On Error GoTo 0
    
    If cellRef Is Nothing Then
        MsgBox "No cell selected. Exiting."
        Exit Sub
    End If
    
    startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)
    
    ' Get the period type and the number of years in the analysis period
    periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
    analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")
    
    ' Get the cell where the date header should begin
    On Error Resume Next
    Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8)
    On Error GoTo 0
    
    If dateHeaderStart Is Nothing Then
        MsgBox "No cell selected for the date header. Exiting."
        Exit Sub
    End If
    
    ' Calculate the number of periods
    Dim numPeriods As Integer
    Select Case UCase(periodType)
        Case "M"
            numPeriods = analysisPeriod * 12
        Case "Q"
            numPeriods = analysisPeriod * 4
        Case "Y"
            numPeriods = analysisPeriod
        Case Else
            MsgBox "Invalid period type entered. Please use M, Q, or Y."
            Exit Sub
    End Select
    
    numPeriods = numPeriods + 1
    
    ' Add labels to the rows
    dateHeaderStart.Offset(0, -1).Value = "Period Ending"
    dateHeaderStart.Offset(0, -1).HorizontalAlignment = xlRight
    dateHeaderStart.Offset(1, -1).Value = "Analysis Month"
    dateHeaderStart.Offset(1, -1).HorizontalAlignment = xlRight
    
    If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
        dateHeaderStart.Offset(2, -1).Value = "Analysis Year"
        dateHeaderStart.Offset(2, -1).HorizontalAlignment = xlRight
    End If
    
    ' Generate the date header and numeric value rows
    Dim i As Integer
    dateHeaderStart.NumberFormat = "MM/DD/YYYY"
    dateHeaderStart.Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
    dateHeaderStart.Offset(1, 0).Value = 0
    
    ' Add the optional third row for monthly or quarterly periods
    If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
        dateHeaderStart.Offset(2, 0).Value = 0
    End If
    
    For i = 2 To numPeriods
        dateHeaderStart.Offset(0, i - 1).NumberFormat = "MM/DD/YYYY"
        
        Select Case UCase(periodType)
            Case "M"
                dateHeaderStart.Offset(0, i - 1).Formula = "=E

Looks like you stopped. Please continue from: Case "M" dateHeaderStart.Offset(0, i - 1).Formula = "=E

Apologies for the truncation. Continuing the code from where it was cut off:

vbaCopy code
        Select Case UCase(periodType)
            Case "M"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 1)"
            Case "Q"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 3)"
            Case "Y"
                dateHeaderStart.Offset(0, i - 1).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 2).Address & ", 12)"
        End Select
        
        dateHeaderStart.Offset(1, i - 1).Formula = "=" & dateHeaderStart.Offset(1, i - 2).Address & " + 1"
        
        ' Add the optional third row for monthly or quarterly periods
        If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
            If UCase(periodType) = "M" Then
                dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/12, 0)"
            ElseIf UCase(periodType) = "Q" Then
                dateHeaderStart.Offset(2, i - 1).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i - 1).Address & "/4, 0)"
            End If
        End If
    Next i

End Sub

This is the complete code, including the addition of labels to each row and the previous updates.

Sorry. I wasn't clear. I want to Period Ending label to begin in the dateHeaderStart (and the Analysis Month, and Analysis Year to be immediately below that). Then, each row will start one column to the right. Is there a simply change you can make to the code to accomplish that?

Ah, I see. You'd like the labels to be in the dateHeaderStart cell and the actual data to begin in the next column to the right. In that case, we just need to make some adjustments to the existing code:

Change the starting position of the date header data to the next column by updating the dateHeaderStart.Offset() arguments in the loop.
Modify the labels assignment to put the labels in the dateHeaderStart cell and below.

Here are the updated sections of the code:

vbaCopy code
' Add labels to the rows
dateHeaderStart.Value = "Period Ending"
dateHeaderStart.HorizontalAlignment = xlRight
dateHeaderStart.Offset(1, 0).Value = "Analysis Month"
dateHeaderStart.Offset(1, 0).HorizontalAlignment = xlRight

If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
    dateHeaderStart.Offset(2, 0).Value = "Analysis Year"
    dateHeaderStart.Offset(2, 0).HorizontalAlignment = xlRight
End If

' Generate the date header and numeric value rows
Dim i As Integer
dateHeaderStart.Offset(0, 1).NumberFormat = "MM/DD/YYYY"
dateHeaderStart.Offset(0, 1).Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
dateHeaderStart.Offset(1, 1).Value = 0

' Add the optional third row for monthly or quarterly periods
If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
    dateHeaderStart.Offset(2, 1).Value = 0
End If

For i = 2 To numPeriods
    dateHeaderStart.Offset(0, i).NumberFormat = "MM/DD/YYYY"

Replace the existing sections in the full code with these updated sections. Now, the labels will be placed in the dateHeaderStart cell and below, and the data will start in the next column to the right.

Unfortunately, that didn't work. It seems the period 0 and the period 1 through n refers to the previous cell. And since period 1 refers to a label cell (and no period 0 cell), it returns a #VALUE!. Please modify the code such that the labels are in the dateHeaderStart column, period 0 starts in the column immediately to the right (and remember the date of Period 0 is EOMONTH(analysis start date, 0), and period 1 starts to the right of that.

I apologize for the confusion. We'll make the necessary changes to ensure that the labels are in the dateHeaderStart column and the period 0 data starts in the next column. Here's the updated code:

vbaCopy code
' Add labels to the rows
dateHeaderStart.Value = "Period Ending"
dateHeaderStart.HorizontalAlignment = xlRight
dateHeaderStart.Offset(1, 0).Value = "Analysis Month"
dateHeaderStart.Offset(1, 0).HorizontalAlignment = xlRight

If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
    dateHeaderStart.Offset(2, 0).Value = "Analysis Year"
    dateHeaderStart.Offset(2, 0).HorizontalAlignment = xlRight
End If

' Generate the date header and numeric value rows
Dim i As Integer
dateHeaderStart.Offset(0, 1).NumberFormat = "MM/DD/YYYY"
dateHeaderStart.Offset(0, 1).Formula = "=EOMONTH(" & cellRef.Address & ", -1)"
dateHeaderStart.Offset(1, 1).Value = 0

' Add the optional third row for monthly or quarterly periods
If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
    dateHeaderStart.Offset(2, 1).Value = 0
End If

For i = 2 To numPeriods
    dateHeaderStart.Offset(0, i).NumberFormat = "MM/DD/YYYY"

    Select Case UCase(periodType)
        Case "M"
            dateHeaderStart.Offset(0, i).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 1).Address & ", 1)"
        Case "Q"
            dateHeaderStart.Offset(0, i).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 1).Address & ", 3)"
        Case "Y"
            dateHeaderStart.Offset(0, i).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 1).Address & ", 12)"
    End Select

    dateHeaderStart.Offset(1, i).Formula = "=" & dateHeaderStart.Offset(1, i - 1).Address & " + 1"

    ' Add the optional third row for monthly or quarterly periods
    If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then
        If UCase(periodType) = "M" Then
            dateHeaderStart.Offset(2, i).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i).Address & "/12, 0)"
        ElseIf UCase(periodType) = "Q" Then
            dateHeaderStart.Offset(2, i).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i).Address & "/4, 0)"
        End If
    End If
Next i

This updated code places the labels in the dateHeaderStart column, period 0 in the next column, and periods 1 through n in the columns to the right.

Let's fix these lines: dateHeaderStart.Offset(1, 0).Value = "Analysis Month" dateHeaderStart.Offset(1, 0).HorizontalAlignment = xlRight It should have conditional logic such that if the user selects M, it should output 'Analysis Month'. But if the user selects Q, it should output 'Analysis Quarter'.

You can update the code by adding an If statement to handle the different label text based on the user's selection. Replace the existing lines with the following code:

vbaCopy code
If UCase(periodType) = "M" Then
    dateHeaderStart.Offset(1, 0).Value = "Analysis Month"
ElseIf UCase(periodType) = "Q" Then
    dateHeaderStart.Offset(1, 0).Value = "Analysis Quarter"
Else
    dateHeaderStart.Offset(1, 0).Value = "Analysis Year"
End If
dateHeaderStart.Offset(1, 0).HorizontalAlignment = xlRight

This code will now display "Analysis Month" when the user selects "M", "Analysis Quarter" when the user selects "Q", and "Analysis Year" for any other case.

Great. I made a few minor changes to the code. Can you spot the changes? Option Explicit Sub GenerateDateHeader() 'Created by Spencer Burton with the help of ChatGPT (GPT-4) 4.22.2023 'Turn off screenupdating Application.ScreenUpdating = False ' Option to run tool or not Dim answer As Variant answer = MsgBox("Do you want to automatically create a financial model date header?", vbQuestion + vbYesNo + vbDefaultButton2, "Create Date Header?") If answer = vbYes Then ' Define the necessary variables Dim ws As Worksheet Set ws = ActiveSheet Dim startDate As Date Dim analysisPeriod As Integer Dim periodType As String Dim cellRef As Range Dim dateHeaderStart As Range ' Get the cell containing the Analysis Start Date On Error Resume Next Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8) On Error GoTo 0 If cellRef Is Nothing Then MsgBox "No cell selected. Exiting." Exit Sub End If startDate = Application.WorksheetFunction.EoMonth(cellRef, -1) ' Get the period type and the number of years in the analysis period periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):") analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):") ' Get the cell where the date header should begin On Error Resume Next Set dateHeaderStart = Application.InputBox("Select the cell where the date header should begin:", Type:=8) On Error GoTo 0 If dateHeaderStart Is Nothing Then MsgBox "No cell selected for the date header. Exiting." Exit Sub End If ' Calculate the number of periods Dim numPeriods As Integer Select Case UCase(periodType) Case "M" numPeriods = analysisPeriod * 12 Case "Q" numPeriods = analysisPeriod * 4 Case "Y" numPeriods = analysisPeriod Case Else MsgBox "Invalid period type entered. Please use M, Q, or Y." Exit Sub End Select numPeriods = numPeriods + 1 ' Add labels to the rows dateHeaderStart.Value = "Period Ending" dateHeaderStart.HorizontalAlignment = xlRight If UCase(periodType) = "M" Then dateHeaderStart.Offset(1, 0).Value = "Analysis Month" ElseIf UCase(periodType) = "Q" Then dateHeaderStart.Offset(1, 0).Value = "Analysis Quarter" Else dateHeaderStart.Offset(1, 0).Value = "Analysis Year" End If dateHeaderStart.Offset(1, 0).HorizontalAlignment = xlRight If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then dateHeaderStart.Offset(2, 0).Value = "Analysis Year" dateHeaderStart.Offset(2, 0).HorizontalAlignment = xlRight End If ' Generate the date header and numeric value rows Dim i As Integer dateHeaderStart.Offset(0, 1).numberFormat = "MM/DD/YYYY" dateHeaderStart.Offset(0, 1).Formula = "=EOMONTH(" & cellRef.address & ", -1)" dateHeaderStart.Offset(1, 1).Value = 0 ' Add the optional third row for monthly or quarterly periods If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then dateHeaderStart.Offset(2, 1).Value = 0 End If For i = 2 To numPeriods dateHeaderStart.Offset(0, i).numberFormat = "MM/DD/YYYY" Select Case UCase(periodType) Case "M" dateHeaderStart.Offset(0, i).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 1).address & ", 1)" Case "Q" dateHeaderStart.Offset(0, i).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 1).address & ", 3)" Case "Y" dateHeaderStart.Offset(0, i).Formula = "=EOMONTH(" & dateHeaderStart.Offset(0, i - 1).address & ", 12)" End Select dateHeaderStart.Offset(1, i).Formula = "=" & dateHeaderStart.Offset(1, i - 1).address & " + 1" ' Add the optional third row for monthly or quarterly periods If UCase(periodType) = "M" Or UCase(periodType) = "Q" Then If UCase(periodType) = "M" Then dateHeaderStart.Offset(2, i).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i).address & "/12, 0)" ElseIf UCase(periodType) = "Q" Then dateHeaderStart.Offset(2, i).Formula = "=ROUNDUP(" & dateHeaderStart.Offset(1, i).address & "/4, 0)" End If End If Next i End If 'Turn on screenupdating Application.ScreenUpdating = True End Sub

I noticed the following changes in the code:

You added the following lines at the beginning of the code to provide the user with an option to run the tool or not:

vbaCopy code
    ' Option to run tool or not
    Dim answer As Variant
    answer = MsgBox("Do you want to automatically create a financial model date header?", vbQuestion + vbYesNo + vbDefaultButton2, "Create Date Header?")
    If answer = vbYes Then

And you added the corresponding End If near the end of the code.

You added a comment at the beginning of the code indicating that it was created with the help of ChatGPT (GPT-4):

vbaCopy code
    'Created by Spencer Burton with the help of ChatGPT (GPT-4) 4.22.2023

You added a new comment at the end of the code to indicate that screen updating is being turned on again:

vbaCopy code
    'Turn on screenupdating

These changes make the macro more user-friendly, providing the option to run it or not, and also help clarify the process with additional comments.

Excellent. There is a bug we need to fix. Currently, the macro assumes that there is a value in the cell selected for the 'startDate'. However, in some cases users will select a cell that doesn't yet have a date in that cell. I think we need to prompt the user to 'Select the cell that contains the Analysis Start Date Input or Create an Analysis Start Date Before Continuing' at which point the macro would end. What do you think? Is this the easiest/best solution, or would you suggest a different solution?

Yes, your solution to prompt the user with a message and end the macro if the cell is empty is a good way to handle the situation. Here's an updated version of the code that checks for an empty cell and displays a message if the cell is empty, and then exits the macro:

After getting the cell containing the Analysis Start Date, add a check for an empty cell, display a message if necessary, and exit the macro if the cell is empty.

vbaCopy code
' Get the cell containing the Analysis Start Date
On Error Resume Next
Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
On Error GoTo 0

If cellRef Is Nothing Then
    MsgBox "No cell selected. Exiting."
    Exit Sub
End If

' Check if the selected cell is empty, display a message, and exit the macro if necessary
If IsEmpty(cellRef) Then
    MsgBox "Selected cell is empty. Please select a cell with the Analysis Start Date or create an Analysis Start Date before continuing."
    Exit Sub
End If

startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)

This way, the macro will handle cases when users select an empty cell for the Analysis Start Date, providing an appropriate message and allowing them to rectify the situation before running the macro again.

Let's make a couple tweaks. 1) Let's label the dialog box that appears 'No Analysis Start Date Selected'; 2) Rather than simply ending the macro, let's give them the option to 'Try Again' or 'Close'

You can modify the code to display a message box with the custom title 'No Analysis Start Date Selected' and provide the user with the option to 'Try Again' or 'Close' using a custom message box. Here's the updated code:

vbaCopy code
' Get the cell containing the Analysis Start Date
TryAgain:
On Error Resume Next
Set cellRef = Application.InputBox("Select the cell containing the Analysis Start Date:", Type:=8)
On Error GoTo 0

If cellRef Is Nothing Then
    MsgBox "No cell selected. Exiting."
    Exit Sub
End If

' Check if the selected cell is empty, display a message, and give the option to try again or close
If IsEmpty(cellRef) Then
    Dim msgResponse As VbMsgBoxResult
    msgResponse = MsgBox("Selected cell is empty. Please select a cell with the Analysis Start Date or create an Analysis Start Date before continuing.", vbRetryCancel + vbCritical, "No Analysis Start Date Selected")
    
    If msgResponse = vbRetry Then
        GoTo TryAgain
    Else
        Exit Sub
    End If
End If

startDate = Application.WorksheetFunction.EoMonth(cellRef, -1)

This code adds a label TryAgain before the InputBox that gets the cell containing the Analysis Start Date. If the selected cell is empty, a message box with the custom title 'No Analysis Start Date Selected' is shown, and the user can choose 'Retry' or 'Cancel'. If the user selects 'Retry', the code will jump back to the TryAgain label and let the user select a cell again. If the user selects 'Cancel', the macro will exit.

Great. That worked. Found another issue. The 'Enter the period type' and Enter the number of years' InputBoxes include a cancel button. If the user clicks that 'Cancel' button, the macro should end.

To handle the Cancel button in the InputBox for both period type and number of years, you can add the following code after each InputBox:

vbaCopy code
If periodType = "" Then Exit Sub

and

vbaCopy code
If analysisPeriod = 0 Then Exit Sub

Here's the updated code with the changes incorporated:

vbaCopy code
' Get the period type and the number of years in the analysis period
periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")

' Exit macro if Cancel button is clicked in periodType InputBox
If periodType = "" Then Exit Sub

analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")

' Exit macro if Cancel button is clicked in analysisPeriod InputBox
If analysisPeriod = 0 Then Exit Sub

This will make sure that if the user clicks the Cancel button in either the period type or the number of years InputBox, the macro will exit gracefully.

Just to confirm. I replace the following code with the code you shared above? ' Get the period type and the number of years in the analysis period periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):") analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")

Yes, that's correct. Replace the following code:

vbaCopy code
' Get the period type and the number of years in the analysis period
periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")
analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")

with the updated code I shared above:

vbaCopy code
' Get the period type and the number of years in the analysis period
periodType = InputBox("Enter the period type (M for Month, Q for Quarter, Y for Year):")

' Exit macro if Cancel button is clicked in periodType InputBox
If periodType = "" Then Exit Sub

analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")

' Exit macro if Cancel button is clicked in analysisPeriod InputBox
If analysisPeriod = 0 Then Exit Sub

This will handle the Cancel button properly for both period type and number of years InputBoxes.

This code worked for the periodType. However, when I click Cancel for the analysisPeriod I get a 'Run-time error 13: type mismatch. And then it highlights this code: analysisPeriod = InputBox("Enter the number of years in the analysis period (e.g., 10):")

My apologies for the confusion. The error occurs because the InputBox returns a string value when you click the Cancel button. To avoid the type mismatch error, let's first store the result in a string variable and check if it's empty before converting it to an integer.

Replace the current analysisPeriod code with the following:

vbaCopy code
Dim analysisPeriodStr As String

analysisPeriodStr = InputBox("Enter the number of years in the analysis period (e.g., 10):")

' Exit macro if Cancel button is clicked in analysisPeriod InputBox
If analysisPeriodStr = "" Then Exit Sub

analysisPeriod = CInt(analysisPeriodStr)

This will prevent the type mismatch error when you click the Cancel button for the analysisPeriod InputBox.

Thank you - we're all set!

You're welcome! I'm glad we could address all the issues and improve the macro together. If you have any further questions or need assistance in the future, please don't hesitate to ask. Have a great day!