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.

Answer 1

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