I have a macro where a user is prompted to enter a date (4/27/21 for example) and then vba will search a named range (date_range) for EVERY cell that has the input. Then I need to copy the entire row where the date was found and paste it onto another worksheet. I have attached a snippet of the worksheet range that I am searching, it is much larger but as an example should be okay. Some of the date fields in the range are populated by a formula and when they're copied over to a new sheet there is an error. I have two issues:
1.) This code only returns the first that finds the date. I know I need to use a loop but I haven't found any success using a For Each. What loop or other code would I need to use in order to return all of the rows that contain the input?
2.) How can I fix the formula errors when copying over the rows to another worksheet?
Here is my code:
1.) This code only returns the first that finds the date. I know I need to use a loop but I haven't found any success using a For Each. What loop or other code would I need to use in order to return all of the rows that contain the input?
2.) How can I fix the formula errors when copying over the rows to another worksheet?
Here is my code:
Sub Search()
'
' Search Macro
' Search
'
' Keyboard Shortcut: Ctrl+x
'
Dim key As Variant
key = InputBox("Please enter a date", "Search")
Dim rngFoundCell As Range
With Range("date_range")
Set rngFoundCell = .Find(What:=key, _
After:=.Cells(.Cells.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngFoundCell Is Nothing Then
rngFoundCell.EntireRow.Copy Sheets("Search").Range("A2")
Else
MsgBox "There was no matching cell found in the worksheet."
End If
End With
End Sub