0

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 works if I step through it in the debugger. but when I run it, excel freezes for awhile before crashing. The data range is $G$5:$AS$175. is that too big to search for a user inputted date?

2.) How can I fix the formula errors when copying over the rows to another worksheet?

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

smaller example range

Results from running above code

ProfoundHypnotic
  • 38
  • 4
  • Research `Find`/`FindNext`. – BigBen May 04 '21 at 16:21
  • @BigBen thank you for your suggestion. that answered my first question. i now have every row that contains the user input. however the formula piece is still occurring. any idea what to research on that front? – ProfoundHypnotic May 04 '21 at 18:42
  • Depends on your formulas. I'd personally just paste the values. – BigBen May 04 '21 at 18:44
  • @BigBen are you referring to PasteSpecial? If you're talking about manually copying and pasting doesn't that kind of defeat the purpose of doing this via VBA. – ProfoundHypnotic May 04 '21 at 19:33
  • Yes `PasteSpecial xlPasteValues`, or you can use value transfer, demonstrated [here](https://stackoverflow.com/questions/24294923/how-to-copy-only-values-in-excel-vba-from-a-range). – BigBen May 04 '21 at 19:34
  • You're Excel freeze coz you enter an infinite loop... Your loop while condition should be `Loop While c.address <> firstaddress`. For the other part, I think BigBen is right. If you don't need the formula on the new sheet, just past values. – SomeDude May 04 '21 at 21:07

1 Answers1

0

I was able to figure this out. here is the code below

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
ProfoundHypnotic
  • 38
  • 4