How do I insert an input box that will ask for a date and then add to Rows after that date?

Question

Sub date
    Dim DTE as Date
    MBox = InputBox(“Enter Friday date”)
    If IsDate(MBox) Then
        DTE = CDate(MBox)
        Range(“F” & Rows.Count).End(xlUp).Offset(1).Select
        Selection.Insert Shift:=xlDown
        Selection.Insert Shift:=xlDown
     Else 
         MsgBox(“This isn’t a date. Try again.”)
    End if
End sub

I need the code to add two rows after it finds the date the user enters in the input box and then add up the values in column D. I realize the range row is incorrect but I am not sure how to insert the rows after the date I entered.

You will want to use the `Range.Find` method most likely, but this is highly dependent on the formats being correct. On the spreadsheet, do the dates have time stamps or is the decimal 0? — urdearboy, May 01 '19 at 16:34
They are not time stamped and when I put range.find, it comes up as an error “argument not optional.” — Djhans26, May 01 '19 at 16:38

score 1 · Accepted Answer · answered May 01 '19 at 16:41

1

A couple of things

You cannot name your sub date as this is a reserved word
No need for .Selection
If you use Application.Inputbox, you can control the input type (to some degree)

Assumes you are working on Sheet1 and your dates that are to be matched against the user input span Column F

Sub Date_Finder()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<-- Update
Dim xInput As Date, Found As Range

xInput = Application.InputBox("Enter Date [mm/dd/yyyy]", Type:=1)

    If IsDate(xInput) Then
        Set Found = ws.Range("F:F").Find(xInput)

        If Found Is Nothing Then
            MsgBox "Input not found in range"
        Else
            Found.Offset(1).EntireRow.Insert (xlShiftDown)
            Found.Offset(1).EntireRow.Insert (xlShiftDown)                
        End If
    Else
        MsgBox "Invalid Entry. Ending sub" & vbNewLine & "Entry: " & xInput, vbCritical
    End If

End Sub

answered May 01 '19 at 16:41

urdearboy

14,439
5
28
58

How would I add a sum function to the first empty row I after the data and adding everything above it up to cell D4? – Djhans26 May 01 '19 at 16:56
1

That is a completely different question. Give it some time to try to figure that out - if you cant, new questions get new posts. If this answered the question in your initial post, you should accept the solution :) – urdearboy May 01 '19 at 16:57
This finds the first cell that contains a date I enter. How do I modify it to come from bottom up instead of top down? – Djhans26 May 01 '19 at 17:01
1

You can amend options on the `Range.Find` method. [This](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) link will tell you exactly how – urdearboy May 01 '19 at 17:02
How do I get this to where if the date is not found, it will let the user retry? – Djhans26 Jun 03 '19 at 20:07
https://stackoverflow.com/questions/31999974/do-until-loop-inputbox-infinitely-loops – urdearboy Jun 04 '19 at 15:04
@Djhans26 please post a new question as this is a new issue and not in your initial question. New questions get new posts. If questions were allowed to continuously evolve, no question would ever really be answered. – urdearboy Jun 04 '19 at 15:33
Here is one more link that shows you how to do this with a date https://stackoverflow.com/questions/19430765/input-box-date-range-in-vba – urdearboy Jun 04 '19 at 15:33

How do I insert an input box that will ask for a date and then add to Rows after that date?

1 Answers1