CODEWITHSUNDEEP
vbaexcel
Sidvi
Sidvi

Reputation: 101

Searching for a string of text in a specific worksheet

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

Upvotes: 1

Views: 77

Answers (2)

Rory
Rory

Reputation: 34045

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

Upvotes: 2

Vityata
Vityata

Reputation: 43585

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

Upvotes: 1

Related Questions