Thread: Find & Replace Text in Multiple WS

Hello all,

Need help in a VB macro - I have 17 worksheets in one excel file which has a lot of text. Each column has words such as Apple Icecream or Banana Sundae...etc.

In one of the worksheets i have listed acronyms such as Apple Icecream (cell A1) APPL IC (Cell b1), Banana Sundae (cell A2) BAN SD (cell b2)......

I have 100 such acronyms and want to change words across all sheets. Current i have the below noted code but there are 2 issues i face. 1) it doesnt do the typical find and replace. If the cell has Apple Icecream Large it would not replace it. 2) Its made to find and replace just one worksheet and not all in this workbook. Please help!!


Dim lookup As String
Dim replace As String


Sub find_replace()

Application.ScreenUpdating = False

r = LastRow()
Dim lr As Integer

For lr = r To 1 Step -1

Workbooks("Mapping_Macro.xls").Worksheets("Mapping").Activate

lookup = Cells(lr, 1).Value
replace = Cells(lr, 2).Value

Workbooks("Mapping_Macro.xls").Worksheets("abc").Select


Cells.replace _
What:=lookup, Replacement:=replace, _
LookAt:=xlWhole, MatchCase:=False


Next lr

End Sub

Function LastRow() As Long

Dim ix As Long
ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastRow = ix
End Function
[/FONT]