How can I search for a string in multiple Wksheets simultaneously?

Question

I have around 30 sheets that I want this code to run in at the same time. I want to find "ABC" and delete the value of the cell next to it in all my worksheets.

I get my error from: Set rSearch = .**range**("A1", .range("A" & rows.count).end(x1up))

When I have specified "Sheet1" next to the "With" statement, it works, but I want this code to run on all my sheets.

Sub soek()

    Dim rSearch As Range
    Dim rFound As Range
    Dim sign12 As String
    Dim sheetsarray As Sheets
    Set sheetsarray = ActiveWorkbook.Sheets(Array("sheet1", "sheet2", "sheet3"))


    sign12 = "ABC"


    With sheetsarray
        Set rSearch = .Range("A1", .Range("A" & Rows.Count).End(xlUp))

        Set rFound = rSearch.Find(What:=sign12, LookIn:=xlValues)

        If rFound Is Nothing Then

        Else
            rFound.Offset(0, 1).ClearContents

        End If
    End With

End Sub

This question is a lot like: How to search for a string in all sheets of an Excel workbook?

But in my opinion, it's a lot easier to understand how to make code run on additional sheets reading my code than the code from the link above.

Add a `For Each` loop over `sheetsarray`, put the `With` block inside it. — GSerg, Sep 18 '18 at 12:05
Set a `WorkBook` object (i.e. `Set oWB = ThisWorkbook`). Now you can loop through all your sheets in a loop (i.e. `For Each oWS in oWB.Worksheets`.. set oWS as: `Dim oWS as Worksheet`) — Zac, Sep 18 '18 at 12:06
The Go-To-Solution is a For Each loop, as @GSerg and Zac suggested — prextor, Sep 18 '18 at 12:11
What you are describing is called multi-threading and AFAIK Excel VBA doesn't support it.. Best to go for the For Each loop as already stated — Glitch_Doctor, Sep 18 '18 at 12:29
Thanks for all the answeres, but i am not quite sure how the "for each" solution works. Is it possible for some1 to type it as an answere with the suggested change in the code? (I have never use For each myself) — Sam, Sep 18 '18 at 12:36
See [this](https://stackoverflow.com/questions/20422356/loop-through-excel-sheets#20422491) — cybernetic.nomad, Sep 18 '18 at 12:38
@cybernetic.nomad - post an answer? If nobody else has I'll post one in half an hour. — SJR, Sep 18 '18 at 12:42
I tried to add the "For each sheetsarray in ThisWorkbook.sheets", and now i get error in: "Set rSearch = .Range("A1", .**Range**("A" & Rows.Count).End(xlUp))" — Sam, Sep 18 '18 at 12:44
Possible duplicate of [search for string in all sheets of an excel file using macros](https://stackoverflow.com/questions/4734794/search-for-string-in-all-sheets-of-an-excel-file-using-macros) — cybernetic.nomad, Sep 18 '18 at 13:10
@cybernetic.nomad I disagree, I think if anything the other should be closed as a dupe of this; that one is just a wall of overly-complex code and this one is simple and more easily applicable to individual situations. — TylerH, Sep 18 '18 at 13:54

score 1 · Accepted Answer · answered Sep 18 '18 at 13:11

Try this (compilation of the comments above ;-)

Sub soek()

Dim rSearch As Range
Dim rFound As Range
Dim sign12 As String
Dim oWB As Workbook
Dim oWS As Worksheet

Set oWB = ThisWorkbook

sign12 = "ABC"

For Each oWS In oWB.Sheets

With oWS
    Set rSearch = .Range("A1", .Range("A" & Rows.Count).End(xlUp))

    Set rFound = rSearch.Find(What:=sign12, LookIn:=xlValues)

    If rFound Is Nothing Then

    Else
        rFound.Offset(0, 1).ClearContents

    End If
End With

Next oWS

End Sub

How can I search for a string in multiple Wksheets simultaneously?

1 Answers1

Linked