Excel VBA: Find date range from user input

Question

So I have a workbook with multiple sheets, each row in each sheet is for a different product and has a date the product arrived along with some other info.

I have a sheet called "GRN-Date Search" where I am allowing users to input specific info and have VBA search through the sheets and copy and paste information.

I have hit a wall when it comes to getting it to search for a user defined date range though.

Here is what I have for a single date to give you an idea. I am new to VBA so I am not sure if it is even possible to use the .find function for a date range?

Any help you can offer would be appreciated.

Sub DateSearch_Click()

    If Range("B3") = "" Then
        MsgBox "You must enter a date to search"
        Range("B3").Select
        Exit Sub
    Else
        'Clear "GRN-Date Search" Sheet Row  through End
            Sheets("GRN-Date Search").Range("A7:A" & Rows.Count).EntireRow.Clear
        'Set myDate variable to value in B3
            myDate = Sheets("GRN-Date Search").Range("B3")
        'Set initial Paste Row
            nxtRw = 7
        'Loop through Sheets 2 - 29
            For shtNum = 2 To 29
        'Search Column b for date(s)
            With Sheets(shtNum).Columns(1)
             Set d = .Find(myDate)
                If Not d Is Nothing Then
                    firstAddress = d.Address
                Do
        'Copy each Row where date is found to next empty Row on Summary sheet
                d.EntireRow.Copy Sheets("GRN-Date Search").Range("A" & nxtRw)
                nxtRw = nxtRw + 1
                Set d = .FindNext(d)
            Loop While Not d Is Nothing And d.Address <> firstAddress
                 End If
        End With
    Next

    End If

End Sub

A.S.H · Accepted Answer · 2017-07-31 05:36:41Z

2

To work with a date range, you need to drop the use of .Find. The best method is to use auto-filtering. The following code uses this feature and supposes that your user enters a range of dates in cells B3 and C3. Also recall that autofilter considers that you have a header row in the filtered range.

Sub DateSearch_Click()
    Dim date1 As Date, date2 As Date, nxtRw As Long, shtNum As Long
    ' Date Range entered in cells B3 and C3
    If Range("B3") = "" Or Range("C3") = "" Then
        MsgBox "You must enter a date to search"
        Range("B3").Select
        Exit Sub
    End If
    date1 = Sheets("GRN-Date Search").Range("B3")
    date2 = Sheets("GRN-Date Search").Range("C3")

    'Clear "GRN-Date Search" Sheet Row  through End
    Sheets("GRN-Date Search").Range("A7:A" & Rows.count).EntireRow.Clear
    nxtRw = 7   'Set initial Paste Row
    For shtNum = 2 To 29 'Loop through Sheets 2 - 29
      With Sheets(shtNum).Range("A5:A" & Sheets(shtNum).Cells(Rows.Count, 1).End(xlUp).Row)
        .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:=">=" & date1, Criteria2:="<=" & date2
        .Offset(1).EntireRow.Copy Sheets("GRN-Date Search").Range("A" & nxtRw)
        nxtRw = nxtRw + .SpecialCells(xlCellTypeVisible).Count - 1
        .AutoFilter
      End With
    Next
End Sub

edited Jul 31, 2017 at 5:36

answered Jul 31, 2017 at 1:35

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

4 Comments

JHM Over a year ago

Thanks for that, running into a few issues though. The .offset(1) line is coming up with an error "application-defined or object defined error". I see the sheet loop also mustn't be working as it is starting at sheet number one and it assumes that the headers are in row 1 whereas they are in row 5.

A.S.H Over a year ago

@JHM I see, we can't offset an entire column. Try the edit. Only thing is that of course I iterate on sheets 2 to 29 as in your original code.

JHM Over a year ago

Okay thanks, I have update the code and it has fixed the initial error however now I am getting an overflow error relating to "nxtRw = nxtRw + .SpecialCells(xlCellTypeVisible).Count - 1"

JHM Over a year ago

Still having issues with this code, is anyone able to provide insight? I can't seem to get past this overflow error.

Collectives™ on Stack Overflow

Excel VBA: Find date range from user input

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related