Geek Answers Handbook

VBA Replace ignores restrictions on columns / sheets

I am trying to use VBA for search / replace. The goal is to iterate over the "Data_Pairs" sheet containing all the pairs to find / replace, and find / replace these pairs only in column A and only in the given range of sheets in the book (which does not include "Data_Pairs").

For some reason, every suitable value is replaced no matter what column it is in. Values ​​are also replaced in sheets whose index is outside the specified range.

Any help would be greatly appreciated.

I am 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 problem: if Data_Pairs A1 = A and Data_Pairs B1 = 1, each value 1 in the entire book is replaced by A.

+4
2

, , Excel 2010, .

, , FIND (, /) WORKBOOK, , :

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

, , , . Replace " ", , Replace ().

Ozgrid - - .Find reset. :

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
+5

Worksheets("Data_Pairs").Activate For ... Next. , , , 9 × , . .Activate, 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

Select Excel VBA macros Select Acticate.

+1

More articles:


All Articles