Search for Date and copy and Paste Whole Row: VBA

Question

I am new to VBA and I am not sure how I can accomplish the following task.

I want to ask the user for a date range and then take that date range and search through column BB of “Source Sheet” for any date in that range. If a date is in that range I want to take the entire row and copy it and then paste it into a different sheet called “Dest Sheet”

Any help is appreciated! I have tried many different ways of doing this and nothing I have done is working. Here is what I currently have

Dim N As Integer
Dim i As Integer
Dim StartDate As Date
Dim EndDate As Date

N = Cells(Rows.Count, "E").End(xlUp).Row    'determines the last row with data in it
                                              'uses column E because E should never be Null if not after last active row

Dim j As Integer 'declares j for indexing of the rows on the target spreadsheet
j = 2

Dim fRow As Long                                    'is the role of j in an attempt to copy and paste
fRow = Sheets("Dest Sheet").UsedRange.Rows.Count         'defines the variable

For i = 2 To N          'indexes 2 to N to begin with row after the title row to the last active ro
Cells(i, "BB").Select
    If Cells(i, "BB").Value <> "" Then

    Columns("BB").Select
    Selection.NumberFormat = "mm/dd/yyyy"

    Range("BB2").End(xlDown).Select

    StartDate = Application.InputBox("Enter the start date")
    EndDate = Application.InputBox("Enter the end date")

    'in row i execute the if statement

        If ("BB" >= StartDate & "BB" <= EndDate) Then

         Sheets("Source Sheet").Cells(i, 1).EntireRow.Copy Destination:=Sheets("Dest Sheet").Cells(fRow, 1).Offset(1, 0)
         fRow = fRow + 1

        End If

 End If  'declares end of the if statements

Next i

End Sub

Read up on Advance Sort - that will make this vba go a lot better. You could even record yourself using it, and go from there as a base — Badja, Mar 13 '19 at 14:04
Also, it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Mar 13 '19 at 14:05
Do you need to format and ask for the start and end dates each iteration of the loop.? — Nathan_Sav, Mar 13 '19 at 14:08
One thing I see is you are comparing a Date, which excel treats as a number, against a string(text) version of the date that the user inputs. — Zack E, Mar 13 '19 at 14:09
Is there a way to force the user input to come in as a date instead of a string? — Cassandra Durell, Mar 13 '19 at 15:20

Tim · Answer 1 · 2019-03-13T15:13:57.597

0

I think the below code might be what you are looking for.

However, what is the purpose of your variable "j"? Also, are the values in column BB not formatted as dates? I wasn't sure the purpose of the line that executes Selection.NumberFormat = "mm/dd/yyyy"

Dim wb As Workbook
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim n As Long
Dim i As Long
Dim fRow As Long
Dim startDate As Date
Dim endDate As Date

Set wb = ActiveWorkbook
Set wsSrc = wb.Sheets("Source Sheet")
Set wsDest = wb.Sheets("Dest Sheet")

n = wsSrc.Range("E:E").Find(what:="*", searchdirection:=xlPrevious).Row
startDate = Application.InputBox("Enter the start date")
endDate = Application.InputBox("Enter the end date")

For i = 2 To n

    If wsSrc.Range("BB" & i).Value >= startDate And wsSrc.Range("BB" & i).Value <= endDate Then

        fRow = wsDest.Range("A:A").Find(what:="").Row
        wsSrc.Range("BB" & i).EntireRow.Copy wsDest.Cells(fRow, 1)

    End If

Next

edited Mar 13 '19 at 15:13

answered Mar 13 '19 at 14:42

Tim

48
8

Hey Tim the j was something I had been previously using as an indexing variable and it should’ve been deleted! – Cassandra Durell Mar 13 '19 at 15:17
As far as your version goes, things look good up until it reaches n. When I get to n I get a run time error for “object variable or with block not set”. Any input on this? – Cassandra Durell Mar 13 '19 at 15:18
It is probably not finding any values populated in column E. It looked like you were using column E to determine the total number of rows in the worksheet. Is that what you intended to do? – Tim Mar 13 '19 at 15:22
To see how it would run without n I commented the n assignment line out and had the loop run from 2 to 100 just to try it and when I do that I get an errror with the if statement “object required” – Cassandra Durell Mar 13 '19 at 15:23
And yes! E is the proper column. I restructured to test some things and didn’t realize it left E empty. Now I just need the if statement to run – Cassandra Durell Mar 13 '19 at 15:25
The line that contains the if statement yields the error – Cassandra Durell Mar 13 '19 at 15:25
I found my issues in the if statement. I have an error in the assignment of fRow now that I am working through. That line is now the issue. – Cassandra Durell Mar 13 '19 at 15:30

Search for Date and copy and Paste Whole Row: VBA

1 Answers1