Excel macro: Inserting row based on user input

Question

In an excel file of which column A is filled with different dates, sorted in ascending order, the macro should generate a user prompt which asks for a date. The macro should then insert a row between the two dates that are smaller and larger than the date given by the user.

So far, I only achieved to prompt the user for a specific row below which the macro then inserts a new row. Can anyone help me out with the next steps?

Sub addRow()
    Dim row
    row = InputBox("Maturity date of new bond", "Input maturity date")
    If Not row = vbNullString Then
        ActiveSheet.Range("A" & row + 1).EntireRow.Insert
    End If
End Sub

Answer 1

Kudos to Taelsin for his answer, but I did notice a few issues with his macro (for example, there is no accounting for the possibility of an invalid date format entered by the user).

Plus, I figured you might want to actually learn what exactly is going on with the code. So, I created the following subroutine with plenty of comments and explanations. I hope it finds you well.

Good luck!

---

Sub addRow()

' ============================================================================================================
' This is sort of the easy part, taken somewhat from the code you provided. We're going to create some
' variables to use later, and give our user an input field for the maturity date.
' NOTE: You said the date was in column A, but in case that isn't always true, I also use a
'       range-selector input box to allow the user to specify the range / column containing maturity dates.
' ============================================================================================================
Dim d As Date ' Create our maturity date
Dim dateColumn As Range ' Create a variable to store our date column
Dim isAscOrder As Boolean ' Create a variable to store a value that will indicate in what direction the dates are sorted
Dim i As Long, j As Long, k As Long, c As Range, r As Range ' Create a few misc variables (I always do this just in case I need them later on)

On Error GoTo cancel ' We want to assume any errors on the next line are caused by the user pressing the "Cancel" button.
Set dateColumn = Application.InputBox("Column containing maturity dates", "Specify maturity date column", Type:=8) ' Show the range-selector input box and store it in our date-column variable

retryDate: ' this is called a "Line Label". We can send user here to retry the next action
On Error GoTo invalidDate ' If the next line causes an error, we will send user to our "invalidDate" label (see below)
d = InputBox("Maturity date of new bond", "Input maturity date") ' Show the input-box and store the date value specified by the user
On Error GoTo 0 ' Set the error-handling back to its default

' ============================================================================================================
' Here comes the slightly more advanced part. The general idea here is that we want to find the spot in which
' this date should fit, but we don't even know in what direction the dates are currently sorted.
' ---------------------------------------------------------------------------------------------------------
' (1) So, first we'll determine the sort direction by comparing the first cell to the last cell.
'       Also note that I am specifying "Column(1)" in case the user entered a range with multiple
'       columns by mistake.
' (2) Next, I'll loop through each cell in the range using the "For each" statement. Within each
'       of these iterations, I will check if the cell's date is greater/less than (this will
'       depend on the sort direction) the maturity date specified by the user.
' (3) Finally, when I find a cell that is greater/less than our maturity date, I will insert a
'       new row before that row.
' ---------------------------------------------------------------------------------------------------------
' Sound good? Okay, here we go . . .
' ============================================================================================================

isAscOrder = (CDate(dateColumn.Cells(1, 1).Value) < CDate(dateColumn.Columns(1).End(xlDown).Value)) ' compare the first and last cells of the first column to determine the sort direction
For Each c In dateColumn.Columns(1).Cells ' begin the "For Each" loop; this will loop through each cell in the first column of our range
    If c.Row() > dateColumn.Parent.UsedRange.Rows.Count() Then Exit Sub ' Proceed only if we have not reached end of the "Used Range" (i.e., the end of our worksheet)
    If isAscOrder Then ' If this is in ascending order, then ...
        If CDate(c.Value) > d Then ' ... we will check for the first cell greater than our maturity date.
            c.EntireRow.Insert shift:=xlShiftDown ' When/if we find it, then insert the new row above the current one, and then ...
            Exit Sub ' ... exit the sub (because we are done).
        End If
    Else ' If this is not in ascending order, then we will assume descending order (duh), and then ...
        If CDate(c.Value) < d Then ' ... we will check for the first cell less than our maturity date.
            c.EntireRow.Insert shift:=xlShiftDown ' When/if we find it, then insert the new row above the current one, and then ...
            Exit Sub ' ... exit the sub (because we are done).
        End If
    End If
Next c ' No greater/less than date was found; proceed to the next iteration of our "For Each" loop (i.e., the next cell).


' ============================================================================================================
' Our code execution shouldn't come down this far (since we handle all possible scenarios above, and each one
' results in exiting the sub. However, we do need to specify some code to handle our errors.
' ============================================================================================================
Exit Sub ' We shouldn't ever get to this point, but exit the sub just in case.
invalidDate:
If MsgBox("Please enter a valid date (i.e.,""mm/dd/yyyy"")", vbRetryCancel, "Invalid Date") = vbCancel Then ' Display the "invalid date" error, and ask the user if he or she would like to retry. If "Cancel" is clicked, then ...
    Exit Sub ' ... exit the sub.
Else ' If the user clicked "Retry", then ...
    GoTo retryDate ' ... send them back to the date input box (i.e., the "retryDate" label).
End If

cancel:
Exit Sub

End Sub

Answer 2

As I said in the comments I would just iterate through your date column until you find a date that is larger (I said smaller in the comments, but if it's ascending order the larger date will be towards the bottom).

Sub addRow()
    Dim givenDate As Date
    givenDate = InputBox("Maturity date of new bond", "Input maturity date")
    If givenDate <> vbNullString Then
        Dim iter As Range
        Set iter = ActiveSheet.Range("A1")
        Do
            Set iter = iter.Offset(1)
        Loop While iter.Value <= givenDate
        iter.EntireRow.Insert xlDown
        Set iter = iter.Offset(-1)
        iter.Value = givenDate
    End If
End Sub

You'll probably need to do more error checking, but this should do the trick.