VBA Replace is Ignoring Column/Sheet Restrictions

Question

I'm trying to use VBA for a find/replace. The goal is to iterate through a "Data_Pairs" sheet which contains all the pairs to find/replace, and to find/replace those pairs only in Column A and only in a specified range of sheets in the workbook (which does not include "Data_Pairs").

For some reason, every matching value is replaced, regardless of which column it's in. Values are also replaced in sheets whose index falls outside the defined range.

Any help would be greatly appreciated.

I'm using the following code:

Sub Replace_Names()

Dim row As Integer
Dim row2 As Integer
Dim sheet As Integer
Dim findThisValue As String
Dim replaceWithThisValue As String

For row = 1 To 10
  Worksheets("Data_Pairs").Activate
  findThisValue = Cells(row, "A").Value
  replaceWithThisValue = Cells(row, "B").Value
  For sheet = 2 To 10
    Worksheets(sheet).Columns("A").Replace What:= findThisValue, Replacement:=replaceWithThisValue     
  Next sheet
Next row
End Sub

To give a concrete example of the issue: if Data_Pairs A1 = A and Data_Pairs B1 = 1, every single value of 1 in the entire workbook is replaced with A.

I just ran this and it only replaced the values in ColumnA for me, Excel 2013 — chancea, Feb 06 '15 at 21:19
Let's make it 3-for-3: This code worked correctly for me in Excel 2010. — David Zemens, Feb 06 '15 at 21:28
I greatly appreciate you all checking! I'm running 2010 64-bit. I'll try running it on a different laptop to see if it works. — LCG, Feb 06 '15 at 21:45
THere is a peculiar circumstance *not* related to the structure of your workbook or the index of the Data_Pairs worksheet, which would cause this behavior, I've added as additional reference/answer below. — David Zemens, Feb 06 '15 at 21:50

score 5 · Accepted Answer · answered Feb 06 '15 at 21:48

I observe this works as-expected in Excel 2010, echoing Greg and chancea's comments above.

HOWEVER, I also observe that if you have previously opened the FIND dialog (for example you were doing some manual find/replace operations) and changed scope to WORKBOOK, then the observed discrepancies will occur, as discussed here:

http://www.ozgrid.com/forum/showthread.php?t=118754

This may be an oversight, because it does not appear to have ever been addressed. While the Replace dialog allows you to specify Workbook versus Worksheet, there is no corresponding argument you can pass to the Replace method (documentation).

Implement the hack from the Ozgrid thread -- for some reason, executing the .Find method seems to reset that. This appears to work:

Sub Replace_Names()

Dim row As Integer
Dim row2 As Integer
Dim sheet As Integer
Dim findThisValue As String
Dim replaceWithThisValue As String
Dim rng As Range

For row = 1 To 10
  Worksheets("Data_Pairs").Activate
  findThisValue = Cells(row, "A").Value
  replaceWithThisValue = Cells(row, "B").Value
  For sheet = 2 To 3
    Set rng = Worksheets(sheet).Range("A:A")
    rng.Find ("*")   '### HACK

    rng.Replace What:=findThisValue, Replacement:=replaceWithThisValue
  Next sheet
Next row
End Sub

This was precisely the issue. I had manually done some CTRL+H replacing with the entire workbook as the scope. Thank you so much! — LCG, Feb 06 '15 at 21:53
Cheers. It would be wise as the other answer suggests, to avoid relying on Select/Activate methods as you continue to work in VBA. Here is a good primer on the topic: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — David Zemens, Feb 06 '15 at 21:54

score 1 · Answer 2 · edited May 23 '17 at 11:56

You have a Worksheets("Data_Pairs").Activate inside your For ... Next loop. That would seem to indicate that the command is called 9× more that it has to be. Better not to reply on .Activate to provide the default parent of Cells.

Sub Replace_Names()
    Dim rw As long, ws As long
    Dim findThis As String, replaceWith  As String

    with Worksheets(1)
      For rw = 1 To 10
        findThis = .Cells(rw , "A").Value
        replaceWith = .Cells(rw , "B").Value
        For ws = 2 To 10  ' or sheets.count ?
          with Worksheets(ws)
            .Columns("A").Replace What:= findThis, Replacement:=replaceWith
          end with
        Next ws
      Next rw
    end with

End Sub

See How to avoid using Select in Excel VBA macros for more on getting away from Select and Acticate.

Thank you, that makes a lot of sense. – LCG Feb 06 '15 at 21:47 — LCG, Feb 06 '15 at 21:47

VBA Replace is Ignoring Column/Sheet Restrictions

2 Answers2