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: 5
  • results.png
    results.png
    12.3 KB · Views: 5

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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]
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top