Macro Replace

Hello,

I have many like ="<a href='"&CURL("http://Google.com">"Google"</a>"
in many sheets .
I need a macro to search and  replace "Google" with other value get it from below, in all sheets
Sheet name Cell       Value
MT                 C5       Bing.com
Thanks
samprgAsked:
Who is Participating?
 
gowflowCommented:
no its ok it is fixed here but still you did not tell me if you need to look also in MT sheet ???
gowflow
Sub ChangeStr()

Dim WS As Worksheet, Wst As Worksheet
Dim Cell As Range
Dim LookFor As String
Dim ReplaceBy As String
Dim FirstAddress As String
Dim I As Long

Set WS = Sheets("MT")
LookFor = WS.Range("C6")
ReplaceBy = WS.Range("C5")

For Each Wst In ActiveWorkbook.Worksheets
    If Wst.Name <> WS.Name Then
        With Wst.UsedRange
            Set Cell = .Find(LookFor, LookIn:=xlValues, lookat:=xlPart)
            If Not Cell Is Nothing Then
                FirstAddress = Cell.Address
                Do
                    Cell = Replace(Cell, LookFor, ReplaceBy)
                    I = I + 1
                    On Error Resume Next
                    Set Cell = .FindNext(Cell)
                Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
                On Error GoTo 0
            End If
        End With
    End If
    
Next Wst

 
MsgBox ("Total of " & I & " Cells containing instances of '" & LookFor & "' where found and replaced by '" & ReplaceBy & "' successfully.")

     
End Sub

Open in new window

0
 
gowflowCommented:
so u need to change google.com to bing.com and what about the "Google" change it to "Bing" ?
If I understood correctly you need to loop thru all the sheets and replace above but what is
Sheet name Cell       Value
MT                 C5       Bing.com
?
gowflow
0
 
samprgAuthor Commented:
If I need to change Bing.com to other one , just I will put the new value in C5 and run the macro


Sheet name Cell       Value
MT                 C5       Bing.com
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
samprgAuthor Commented:
Sorry,
the macro should get the new value from sheet MT C5
0
 
gowflowCommented:
and you need to change only in sheet MT ?
gowflow
0
 
samprgAuthor Commented:
You  got it
0
 
gowflowCommented:
fine you say you have many sheets that contains the info you want to change and you say that what you want to change is in Cell C5 then you confirm that you need to change only sheet MT isn't it redundant ??
gowflow
0
 
samprgAuthor Commented:
The macro takes a new value from sheet MT only, so it isn't redundant.
0
 
gowflowCommented:
ok fine do you have these instances also in sheet MT ???
that was my question

Anyway the below code will change all instances of "Google" to anything found in Cell C5 of sheet MT. It will not look for these instances in sheet MT !! If you need it also to look for these instances in sheet MT let me know I will fix it

gowflow
Sub ChangeStr()
Dim WS As Worksheet, Wst As Worksheet
Dim Cell As Range
Dim ReplaceBy As String
Dim FirstAddress As String
Dim I As Long

Set WS = Sheets("MT")
ReplaceBy = WS.Range("C5")

For Each Wst In ActiveWorkbook.Worksheets
    If Wst.Name <> WS.Name Then
        Set Cell = Wst.Find("Google", LookIn:=xlValues, lookat:=xlPart)
        If Not Cell Is Nothing Then
            FirstAddress = Cell.Address
            Do
                Cell = Replace(Cell, "Google", ReplaceBy)
                I = I + 1
                Set Cell = Wst.FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
    End If
    
Next Wst

 
MsgBox ("Total of " & I & " instances of 'Google' where found and replaced by '" & ReplaceBy & "' successfully.")
     
End Sub

Open in new window

0
 
samprgAuthor Commented:
I got error. see the screenshot
And please I need the macro takes the old value from C6. Thanks
Error.JPG
0
 
gowflowCommented:
Oops revised code
gowflow
Sub ChangeStr()
Dim WS As Worksheet, Wst As Worksheet
Dim Cell As Range
Dim ReplaceBy As String
Dim FirstAddress As String
Dim I As Long

Set WS = Sheets("MT")
ReplaceBy = WS.Range("C5")

For Each Wst In ActiveWorkbook.Worksheets
    If Wst.Name <> WS.Name Then
        Set Cell = Wst.Find("Google", LookIn:=xlValues, lookat:=xlPart)
        If Not Cell Is Nothing Then
            FirstAddress = Cell.Address
            Do
                Cell = Replace(Cell, "Google", ReplaceBy)
                I = I + 1
                Set Cell = Wst.FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
        End If
    End If
    
Next Wst

 
MsgBox ("Total of " & I & " instances of 'Google' where found and replaced by '" & ReplaceBy & "' successfully.")
     
End Sub

Open in new window

0
 
gowflowCommented:
Do not use the second version just chking something our replies crossed.
gowflow
0
 
samprgAuthor Commented:
Gives me same error .
I think the error in " Set Cell = Wst.Find("Google", LookIn:=xlValues, lookat:=xlPart) "
0
 
samprgAuthor Commented:
Awesom
0
 
gowflowCommented:
Your welcome and sorry it took more than it should !!! sometimes I get clumzy !
Tks for the grade !
Cheers
gowflow
0
 
samprgAuthor Commented:
That's OK
0
 
samprgAuthor Commented:
I need you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.