VBA help: search function not bringing up any results

[ExcelGirl1988]

Hi,

I have put together a code which will search between 2 dates in the workbook and then copy and paste the result into a new worksheet but when I run the search it keeps telling me no results were found when I know I have got data in the workbook that meets the dates that I put into the search. Can anyone see what I have done wrong? I am quite new to VBA and still learning.

Sub FindCopy()
 Dim myString1, mystring2, firstAddress As String
 Dim Unionsearch As Range
 Dim Search1 As Range
 Dim Search2 As Range
 Dim nxtRw As Long, i As Integer
 Dim c As Range
 Dim wsDestination As Worksheet
 Dim mySize As XlLookAt
 Dim found As Boolean
 Dim response As VbMsgBoxResult
 Dim start As String, finish As String
 Dim startDate As Date, finishDate As Date, foundDate As Date


startSearch:
'Initialise nxtRw'
nxtRw = 1
'Get input from user'
Do
 found = False
 myString1 = Application.InputBox("Enter the start date", "Start Date")
   Loop While Not IsDate(myString1)
   startDate = CDate(myString1)
'Exit if Cancelled'
 If myString1 = False Then Exit Sub
'Force valid entry'
 If Len(myString1) = 0 Then
 response = MsgBox("The Search Field Can Not Be Left Blank" _
 & vbLf & vbLf & "Do You Want To Try Again?", vbYesNo + vbQuestion, "Search")
Else
'Get end date'
   Do
    mystring2 = Application.InputBox("Enter the end date", "Finish Date")
    Loop While Not IsDate(mystring2)
    finishDate = CDate(mystring2)
'Exit if Cancelled'
 If mystring2 = False Then Exit Sub
'Force valid entry'
 If Len(mystring2) = 0 Then
 response = MsgBox("The Search Field Can Not Be Left Blank" _
 & vbLf & vbLf & "Do You Want To Try Again?", vbYesNo + vbQuestion, "Search")
Else
'add new sheet'
    If wsDestination Is Nothing Then Set wsDestination = Worksheets.Add(After:=Sheets(Sheets.Count))
'look in each worksheet'
    For i = 1 To ThisWorkbook.Worksheets.Count - 1
    With Worksheets(i).UsedRange
'Search usedrange in sheet'
    Set Search1 = .Find(what:=(startDate), LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not Search1 Is Nothing Then
    Set Search2 = .Find(what:=(finishDate), LookIn:=xlValues, LookAt:=mySize, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    Set Unionsearch = Union(Search1, Search2)
    End If
'Perform Copy/Paste/FindNext if myString is found'
     If Not Unionsearch Is Nothing Then
     firstAddress = Unionsearch.Address
     found = True
      Do
'Copy entire Row to next empty Row in destination sheet if date criterion satisfied'
       foundDate = Unionsearch.EntireRow.Cells(2).Value
       If foundDate >= startDate And foundDate <= finishDate Then
        nxtRw = nxtRw + 1
        Unionsearch.EntireRow.Copy wsDestination.Range("A" & nxtRw)
       End If
'Search again'
       Set c = .FindNext(Unionsearch)
'stop when search range complete'
       Loop While Unionsearch.Address <> firstAddress
      End If
     End With
    Next i
'inform user if record not found'
 If Not found Then response = MsgBox(startDate & finishDate & Chr(10) & "Search String Not Found" & vbCrLf & vbCrLf & _
  "Do You Want To Try Again?", vbYesNo + vbQuestion, "Not Found") Else Exit Sub
 End If
'try again'
 Do Until response = vbNo
  If response = vbNo Then Exit Do
 Loop
 End If
End Sub

 

[Fennek]

Hello, after reading the first two or three sentences I scipped the rest, here just an idea, if the datess are in column A

 sub Short_as_possible() with cells(1).currentregion      .Autofilter 1, ">#10/1/2018#", "<#11/1/2018#, xland      .copy sheets(2).cells(1)      .autofilter  end with end sub

Obviously, this is an other concept. regards

 

[ExcelGirl1988]

Hi, I have tried your suggestion but it doesn't seem to work, it seems to have a problem with:

.AutoFilter 1, ">#10/1/2018#", "<#11/1/2018#, xland"

It may be due to my dates being in column B in the spreadsheets, I tried to change the code so that it would select the correct cells to check but I don't seem to be getting anywhere.

Kind Regards

 

[ExcelGirl1988]

OK, so I think I know what is going wrong with my code, I think it is this line:

Set Unionsearch = Union(Search1, Search2)

But I don't know how to fix the error, I get a run-time error 5 when this line of code tries to run. I hope someone can help.

 

[Fennek]

push