CODEWITHSUNDEEP
excelvba
Rejoy Mathews
Rejoy Mathews

Reputation: 133

VBA Looping through Worksheets fails when Cells.Find is performed as code

    Sub Macro1()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

    Cells.Find(What:="abc", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.Value = "xyz"

    Next ws

    End Sub

I am using the following code through loop through all the worksheets in an Excel workbook. I want to look for "abc" in a worksheet. If I find "abc" on a particular worksheet I want the value of the cell below it to be set as "xyz". Example if "abc" is found on Cell A2 of Worksheet1, I want Cell A3 to be "xyz". Similarly, If "abc" is found on cell B4 of Worksheet2, I want Cell B5 to be "xyz". This must be done for the entire workbook. However the looping through the entire workbook does not happen and the code just loops through worksheet1.

Upvotes: 0

Views: 109

Answers (1)

user5228244
user5228244

Reputation: 16

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate

Cells.Find(What:="abc", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.Value = "xyz"

Next ws

Upvotes: 0

Related Questions