CODEWITHSUNDEEP
excelvbareplacefind
Ben
Ben

Reputation: 13

Find and replace from the entire workbook instead of just the current sheet

I'm trying to record a simple macro to find <0.01 and <0.1 (match entire cell contents) with 0 across all the workbook. When I run the macro it works the first time. When I reload the sheet, the macro seems to default just find / replace from the current sheet and ignores the rest of the workbook.

Can anyone help please. Below is the simple code that's falling over.

Sub Macro1()
'
' Macro1 Macro
'

'
    Cells.Replace What:="<0.01", Replacement:="0", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="<0.1", Replacement:="0", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Upvotes: 1

Views: 1302

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

The problem is that the user interface can distinguish between …

  1. replace in current worksheet
  2. replace in entire workbook

But if you record a macro for these both scenarios the code will look both times exactly the same. That means you cannot reflect this difference by code properly. The code will do what ever was done last by the user interface.

The only solution to get around this is looping through all the worksheets in your workbook, and replace explicitly in every single worksheet:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Cells.Replace … 'your 1. replace code here

    ws.Cells.Replace … 'your 2. replace code here
Next ws

Upvotes: 1

Related Questions