0
    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.

Community
  • 1
  • 1
Rejoy Mathews
  • 133
  • 8
  • There are some problems with the way you're using `ActiveCell` that might lead to unexpected results, but there's nothing I can see that would cause the loop not to operate. Are you getting an error message of any kind? Is there more than one worksheet in the workbook? (Charts are not included in the `worksheets` collection) – CBRF23 Aug 14 '15 at 16:59
  • There are multiple worksheets in my workbook(No charts included). As mentioned by user5228244 in Answers, using ws.Activate in Workbook looping enabled the code to work fine. – Rejoy Mathews Aug 15 '15 at 07:29

1 Answers1

0
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
user5228244
  • 16
  • Worked perfectly. Thank you! – Rejoy Mathews Aug 15 '15 at 07:26
  • This will throw an error if no match is found. The return from `.Find` is nothing (null reference pointer) when no match is found, so trying to use `.Activate` directly on the return will give you a runtime error for object not found or null reference. Beyond that, using `activecell.offset` will write `123` in some cell, even if no match is found. Lastly, see [how to avoid using select (or activate)](http://%20http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – CBRF23 Aug 15 '15 at 13:03