Searching for a string of text in a specific worksheet

Question

Im trying to make excel search for a text string in a specific column in a specific worksheet that is not the active worksheet. VBA gives me an error that says i cannot use this method of selection. so my question is, do you have a suggestion to do it in another way?

Worksheets("Parts for renovation").Columns("Q:Q").Select
Set cell = Selection.Find(What:="Total transfer price", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then

    Exit Sub

Else

    Worksheets("Internal").Cells(29, 4) = Worksheets("Parts for Renovation").ActiveCell.Offset(0, 4)

End If

score 2 · Accepted Answer · edited Dec 04 '17 at 13:42

2

There is no need to select anything there:

With Worksheets("Parts for renovation").Columns("Q:Q")
    Set cell = .Find(What:="Total transfer price", After:=.Cells(1), LookIn:=xlFormulas, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)    
    If cell Is Nothing Then    
        Exit Sub
    Else
        Worksheets("Internal").Cells(29, 4) = Cell.Offset(0, 4)
    End If
End With

edited Dec 04 '17 at 13:42

Shai Rado

33,032
6
29
51

answered Dec 04 '17 at 13:33

Rory

32,730
5
32
35

2

you were so fast to type, you forgot the `End With` ;) – Shai Rado Dec 04 '17 at 13:38
@ShaiRado Thanks! – Rory Dec 04 '17 at 13:55

score 1 · Answer 2 · answered Dec 04 '17 at 13:38

Your error comes, because you select a range on a non-active worksheet. This is one of the reasons, why you should be avoiding select in general.

However, if you want to make your code working (which is strongly not advisable), you may consider selecting the worksheet before selecting the range:

Worksheets("Parts for renovation").Select
Columns("Q:Q").Select

For the advisable part, try to avoid the usage of "Select" - How to avoid using Select in Excel VBA

Searching for a string of text in a specific worksheet

2 Answers2