Find and Replace

[skelly90]

I have the below script for finding and replacing values on my sheet, but it changes all cases. I only want it to change if the value is in column "D"?

Please can somebody advise.


Many 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

 

[vasim]

Change ws.cells to ws.range("D:D")


in the line

WS.Cells.Replace What:=Search, Replacement:=Replacement, _

 

[OleMiss2010]

Try this:

[pre]

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 ThisWorkbook.Worksheets
WS.Columns("D:D").Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next WS

End Sub

[/pre]

 

[skelly90]

Brilliant, thanks guys!