Search named range for user input and copy/paste row into 2nd worksheet

SkylarP

New Member
Joined
Apr 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:
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
 

Attachments

  • example.png
    example.png
    10.5 KB · Views: 3
  • results.png
    results.png
    12.3 KB · Views: 4

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

SkylarP

New Member
Joined
Apr 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I was able to fix the first issue with the below code however my 2nd issue still persists. Cells that had a formula in it is giving me a !REF error.

VBA Code:
[/QUOTE]
Dim key As Variant
Dim c As Range
Dim firstaddress As String
Dim n As Integer
n = 0

key = InputBox("Please enter a date", "Search")

With Worksheets("Data").Range("date_range")
    Set c = .Find(key, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstaddress = c.Address
    Do
       c.EntireRow.Copy Sheets("Search").Range("A2").Offset(n, 0)
        Set c = .FindNext(c)
        n = n + 1
        Loop While Not c Is Nothing
    End If
End With
[QUOTE]
 

SkylarP

New Member
Joined
Apr 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I was able to solve my issues with the below code
VBA Code:
Dim key As Variant
Dim c As Range
Dim firstaddress As String
Dim n As Integer
Dim rdest As Long

rdest = Sheets("Search").Range("A" & Sheets("Search").Rows.Count).End(xlUp).Row + 1
key = InputBox("Please enter a date", "Search")

With Worksheets("Data").Range("date_range")
Set c = .Find(key, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
   c.EntireRow.Copy
   Sheets("Search").Range("A" & rdest).PasteSpecial Paste:=xlPasteValues
   Set c = .FindNext(c)
   rdest = rdest + 1
Loop While c.Address <> firstaddress
End If
End With
End Sub
 
Solution

Forum statistics

Threads
1,148,257
Messages
5,745,700
Members
423,969
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top