ExcelGirl1988
New Member
- Joined
- Mar 27, 2017
- Messages
- 44
Hi,
I am trying to set up a search function via VBA in my workbook, the aim is for a message box to come up asking for a start date and then when this is entered another message box will come up asking for the end date, this will then search for any of these dates in the workbook and copy and paste the rows into a new worksheet but I keep running into an error where it says 'compile error: Do without Loop'. Please can someone help? The code is below.
I am trying to set up a search function via VBA in my workbook, the aim is for a message box to come up asking for a start date and then when this is entered another message box will come up asking for the end date, this will then search for any of these dates in the workbook and copy and paste the rows into a new worksheet but I keep running into an error where it says 'compile error: Do without Loop'. Please can someone help? The code is below.
Code:
Sub FindCopy()
Dim myString1, mystring2, firstAddress As String
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 c = .Find(myString1, mystring2, LookIn:=xlValues, LookAt:=mySize, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'Perform Copy/Paste/FindNext if myString is found'
If Not c Is Nothing Then
firstAddress = c.Address
found = True
Do
'Copy entire Row to next empty Row in destination sheet if date criterion satisfied'
foundDate = c.EntireRow.Cells(2).Value
If foundDate >= startDate And foundDate <= finishDate Then
nxtRw = nxtRw + 1
c.EntireRow.Copy wsDestination.Range("A" & nxtRw)
End If
'Search again'
Set c = .FindNext(c)
'stop when search range complete'
Loop While c.Address <> firstAddress
End If
End With
Next i
'inform user if record not found'
If Not found Then response = MsgBox(myString1 & mystring2 & 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
Exit Do
Unload (myString1)
Unload (mystring2)
End Sub
Last edited by a moderator:
