PDA

View Full Version : Find/Replace in Specific Workcheet



FShaikh
09-29-2015, 08:04 AM
I am writing a macro to find and replace a certain string within a certain worksheet within a workbook. The macro I have found is below, but searches the entire workbook. Please can someone advise how to amend this to only look in one particular worksheet?


Sub FindReplaceAll()

Dim sht As Worksheet
Dim fnd AsVariant
Dim rplc AsVariant

fnd = "April"
rplc = "May"

ForEach sht In ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht

EndSub

p45cal
09-29-2015, 03:04 PM
Try:
Sub FindReplaceAll()
Dim fnd
Dim rplc

fnd = "April"
rplc = "May"
ActiveWorkbook.Sheets("TheNameOfYourSheetHere").Cells.Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End SubObviously, adjust TheNameOfYourSheetHere

FShaikh
09-30-2015, 06:50 AM
Thanks p45cal - I will try this when I am back in the office later this week. Will keep you posted!

FShaikh
10-05-2015, 02:40 AM
p45cal - Tried your macro and it worked a treat. Many thanks.