?
Solved

Macro  Replace

Posted on 2011-10-30
17
Medium Priority
?
220 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:samprg
  • 9
  • 8
17 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 37053931
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
 

Author Comment

by:samprg
ID: 37053937
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
 

Author Comment

by:samprg
ID: 37053940
Sorry,
the macro should get the new value from sheet MT C5
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 37053941
and you need to change only in sheet MT ?
gowflow
0
 

Author Comment

by:samprg
ID: 37053945
You  got it
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37053953
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
 

Author Comment

by:samprg
ID: 37053964
The macro takes a new value from sheet MT only, so it isn't redundant.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37053998
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
 

Author Comment

by:samprg
ID: 37054041
I got error. see the screenshot
And please I need the macro takes the old value from C6. Thanks
Error.JPG
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37054045
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
 
LVL 31

Expert Comment

by:gowflow
ID: 37054047
Do not use the second version just chking something our replies crossed.
gowflow
0
 

Author Comment

by:samprg
ID: 37054088
Gives me same error .
I think the error in " Set Cell = Wst.Find("Google", LookIn:=xlValues, lookat:=xlPart) "
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 37054118
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
 

Author Closing Comment

by:samprg
ID: 37054148
Awesom
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37054175
Your welcome and sorry it took more than it should !!! sometimes I get clumzy !
Tks for the grade !
Cheers
gowflow
0
 

Author Comment

by:samprg
ID: 37054406
That's OK
0
 

Author Comment

by:samprg
ID: 37054408
I need you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question