Excel VBA find and replace

Question

I have this code and it works fine. Now I need change it and I don't know how do it.

The code searsh in entire wordksheet. I need search in only 1 column

the code search entire cell. I need search left or mid or right side of cell.

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

What do you mean by "the code search entire cell. I need search left or mid or right side of cell."? — Santosh, Jul 16 '13 at 18:24
`WS.Columns(1).Replace What:=Search, Replacement:=Replacement, LookAt:=xlPart, MatchCase:=False` Seems like your code already will replace parts of cell content rather than only the whole text. Unless you mean something else by "left or mid or right side" — Tim Williams, Jul 16 '13 at 18:29

Santosh · Accepted Answer · 2013-07-16T19:02:18.530

4

Is it what you are looking for ?

The below code will look for value in Column A of each sheet.

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.Columns(1).Replace What:=Search, Replacement:=Replacement, LookAt:=xlPart, MatchCase:=False
    Next

End Sub

Updated Answer

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
    Dim cell As Range
    Dim rngFind As Range
    Dim firstCell As String

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

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

    For Each WS In Worksheets
        Set rngFind = WS.Columns(1).Find(What:=Search, LookIn:=xlValues, lookat:=xlPart)

        If Not rngFind Is Nothing Then firstCell = rngFind.Address

        Do While Not rngFind Is Nothing
            rngFind = Replacement & Mid(rngFind, 5, Len(rngFind))
            Set rngFind = WS.Columns(1).FindNext(After:=rngFind)
            If firstCell = rngFind.Address Then Exit Do
        Loop
    Next

End Sub

edited Jul 16 '13 at 19:02

answered Jul 16 '13 at 18:27

Santosh

12,175
4
41
72

Thank you Santosh, this is 50% now I need find only in the first 4 left characters of cell. – Roberto Bahia Jul 16 '13 at 18:30
For example che cell content is: united-united i need find the characters unit and replace to aaaa leaving: aaaaed-united – Roberto Bahia Jul 16 '13 at 18:33
@RobertoBahia Thanks for giving an example. Give me few minutes – Santosh Jul 16 '13 at 18:35
ok. other example cell content santoshcoolman search for A and replace for E in the last characters only then the cell content= santoscoolmEn leave the first a. – Roberto Bahia Jul 16 '13 at 18:39
@RobertoBahia Are you ok to loop ? I am not able to find answer without looping. – Santosh Jul 16 '13 at 18:47
For me no problem. If you have other code I can change all code. – Roberto Bahia Jul 16 '13 at 18:53
@RobertoBahia Please try the code in my updated answer & let me know if it works for you. – Santosh Jul 16 '13 at 19:03
I receive error in this line: If firstCell = rngFind.Address Then Exit Do don't have end if and the exit do is in the same line of IF – Roberto Bahia Jul 16 '13 at 19:13
@RobertoBahia Copy the code as it is and it will work for you. If you find it confusing then put an End if which also would work. – Santosh Jul 16 '13 at 19:17
received Run time error 91 the object variable or with block variable not set in this line If firstCell = rngFind.Address Then Exit Do – Roberto Bahia Jul 16 '13 at 19:19
@RobertoBahia I tried and tested the above code. Its working for me. – Santosh Jul 16 '13 at 19:20
I commit the line and works fine. please explain HOw can I change the code to search in the mid or right of cell – Roberto Bahia Jul 16 '13 at 19:26
@RobertoBahia If you can give an example for mid and right...i can give you the solution – Santosh Jul 16 '13 at 19:33
cell content: RobertABastos I need select and change only the A in the center of cell string cell value after change RobertOBastos – Roberto Bahia Jul 16 '13 at 19:35
Example last characters cell content: Main street Delta I need select the ELT and change it to ddd cell after change Main street Ddddta – Roberto Bahia Jul 16 '13 at 19:37
Try this `p = InStr(1, rngFind, Search, vbTextCompare) - 1 aa = Mid(rngFind, 1, p) & Replacement & Right(rngFind, Len(rngFind) - (p - 1 + Len(Search))) rngFind = aa` – Santosh Jul 16 '13 at 19:50

score 0 · Answer 2 · answered May 06 '16 at 12:23

0

I used this to replace something somewhere.It's simple but works good for me

   Sub test()
Dim x As String, y As String
y = InputBox("Replace what?")
x = InputBox("Replace to?")
    [m12:m20,I2,O7,P5,P6].Replace what:=y, replacement:=x
 End Sub

answered May 06 '16 at 12:23

Deividas

1
1

Excel VBA find and replace

2 Answers2