PDA

View Full Version : VBA for Find & Replace



sswcharlie
12-24-2010, 02:56 PM
I need a similiar code to below from VBA Express site.

Need to find the value in A1 in all sheets and replace (delete row) Do not need any messages.
Code here partly does that. This event code will need to run after entry in A1

Thanks

Option Explicit

Sub ChgInfo()

Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean

Prompt = "What is the original value you want to replace?"
Title = "Search Value Input"
Search = InputBox(Prompt, Title)

Prompt = "What is the replacement value?"
Title = "Search Value Input"
Replacement = InputBox(Prompt, Title)

For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next

End Sub

MWE
01-08-2011, 09:24 PM
I need a similiar code to below from VBA Express site.

Need to find the value in A1 in all sheets and replace (delete row) Do not need any messages.
Code here partly does that. This event code will need to run after entry in A1

Thanks

Option Explicit

Sub ChgInfo()

Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean

Prompt = "What is the original value you want to replace?"
Title = "Search Value Input"
Search = InputBox(Prompt, Title)

Prompt = "What is the replacement value?"
Title = "Search Value Input"
Replacement = InputBox(Prompt, Title)

For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next

End Subif I understand you correctly you wish to:

process all sheets in a workbook
examine cell A1 in each sheet for some value
if the value is found then do something
Your original wording conflicts with the suggested code; in the former you wish to delete row 1 if "X" is found in cell A1 but in the code example, you wish to replace "X" with "Y". So which is it?

Other questions:

if you wish to find "abc" and find "abcdef", is that a match?
if the search case sensitive? for example, if you wish to find "abc" and find "ABC", is that a match?