0

I am trying to have a search by cell value I dont know if the code I have is my best choice. and Im having errors with it, I want to search a cell value from another sheet "Family Ref" in same work book , and when find is done, to copy all rows that include this value and paste is on "view" sheet on a cell/rows range that I choose which always will be same.

with this code the error im having is :select method of range class failed.

any help on my code or suggestion other than my code would appreciate it.

Sub select_rows_with_given_string()

Dim Rng As Range
Dim myCell As Object
Dim myUnion As Range

setmycell = Sheets("view").Range("A2")

Set Rng = Selection

Sheets("Family Ref").Select
Columns("A:M").Select


For Each myCell In Rng
    If InStr(myCell.Text, searchString) Then
        If Not myUnion Is Nothing Then
            Set myUnion = Union(myUnion, myCell.EntireRow)
        Else
            Set myUnion = myCell.EntireRow
        End If
    End If
Next

If myUnion Is Nothing Then
MsgBox "No Registration Found."

Else

myUnion.Select
Sheets("view").Select

Selection.Copy
Range("A5").Select
ActiveSheet.Paste

End If
End Sub
Kavorka
  • 306
  • 3
  • 10
Funny Memo Ms
  • 75
  • 6
  • 1
    Recommended reading: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen May 24 '23 at 17:56
  • thank you, I dont know why my question is closed my code is different than other posts. – Funny Memo Ms May 24 '23 at 17:59
  • You might consider reading the linked thread (recommended reading) and not using `Select` at all. It'll avoid the error. – BigBen May 24 '23 at 18:00
  • In other words, you don't really need `Sheets("Family Ref").Select`, `Columns("A:M").Select`, `myUnion.Select` or `Sheets("view").Select` or `Range("A5").Select`. The linked thread does a good job of explaining how to improve your code. – BigBen May 24 '23 at 18:00
  • sorry again , but thread explains to use parent object I although i am in the same workbook, just selecting rows from different sheet. – Funny Memo Ms May 24 '23 at 18:06
  • I know that I have to post a different question, but I have tried same idea of search with iferror index match and vlookup value all together it also did not work as it retrieves only first row of the find, but I have same value in multiple rows, i dont know how to get around that either. so I tried by code and also errors. – Funny Memo Ms May 24 '23 at 18:08
  • You can't select a range if the parent sheet isn't active. But ... don't select. Selecting is for humans. Code almost always doesn't need to select. – BigBen May 24 '23 at 18:12
  • ok got it Im trying the parent way now, but if error invalid use of me keywords , what am I doing wrong? – Funny Memo Ms May 24 '23 at 18:12
  • Don't use `Me`. Instead `ActiveWorkbook.Worksheets("Yourworksheetname").Activate` ... but again, don't do this. – BigBen May 24 '23 at 18:16
  • I thought the me. is for the parent object which is better that my method . what do u suggest I should do? – Funny Memo Ms May 24 '23 at 18:37

0 Answers0