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

If it's already sorted all you need to do is iterate through the column and find the date that is smaller and insert the row before that date. — Taelsin
– Taelsin, Commented Feb 4, 2016 at 19:21
True, that makes it easier, thanks. However, since I'm fairly new to VBA I was wondering if you could help me out with the correct functions/syntax to do this... — mr_bungles
– mr_bungles, Commented Feb 4, 2016 at 19:38
@mr_bungles Sorry I wasn't part of this convo, I was writing the macro for my answer. I included lots of comments, so it should serve as a tutorial as much as a tool. — Ross Brasseaux
– Ross Brasseaux, Commented Feb 4, 2016 at 21:06

Taelsin · Accepted Answer · 2016-02-04 20:10:05Z

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.

answered Feb 4, 2016 at 20:10

Taelsin

1,09012 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ross Brasseaux · Accepted Answer · 2016-02-05 16:04:28Z

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

Collectives™ on Stack Overflow

Excel macro: Inserting row based on user input

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related