CODEWITHSUNDEEP
vbaexcel
Sam
Sam

Reputation: 67

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

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.

Upvotes: 2

Views: 160

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

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

Upvotes: 1

Related Questions