?
Solved

Macro  Replace

Posted on 2011-10-30
14
Medium Priority
?
232 Views
Last Modified: 2012-06-27
Hello I got Macro 1 hour ago . Please see this link
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27422742.html

It work fine, but it changes also any formula content that cell to formula result.
for eg.
the macro change in B9 only , but changes in p1
 P1 =VLOOKUP(O7,$B$9:$F$17,4,FALSE) to formula result
Thanks
0
Comment
Question by:samprg
  • 8
  • 5
14 Comments
 

Author Comment

by:samprg
ID: 37054768
The code in the below.
I attached test Excel file , please look at D10 in DP sheet before and after  run the code
it shoul does not change.
Thanks

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
 Test.xlsm Test.xlsm
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 37054814
The code above looks for the string found in cell C6 on sheet MT and replaces this string with the contents of cell C5 on sheet MT

In your test file the find value is Yahoo.com and the replace value is Google.com and as expected the string Yahoo.com is replaced with Google.com


Note - The macro runs on the currently selected page when it is started

So I am not sure what the problem is. Could you please provide more info as to what you are expecting

Michael
0
 

Author Comment

by:samprg
ID: 37054830
Before run the macro In D5 "=E10"
After  run the macro D5 " <a href='http://Yahoo.com' 'target='_blank'></a>"
I do not want to change in D5
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!

 

Author Comment

by:samprg
ID: 37055011
After  run the macro in D5 " <a href='http://Google.com' 'target='_blank'></a>"

Why the macro change in D5?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37055156
If I understand correctly, you need to change this line:

                    Cell = Replace(Cell, LookFor, ReplaceBy)

To this:

If not cell.hasformula then Cell = Replace(Cell, LookFor, ReplaceBy)
0
 

Author Comment

by:samprg
ID: 37058011
rorya:
If not cell.hasformula then Cell = Replace(Cell, LookFor, ReplaceBy)
1-Made still in the loop, does not work.

2-What I need to  change any where formula or value because I use this value in some formulas,
 but I want the macro doesn't change in the refernce,
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37058090
In that case I think you need to replace this line:
Set Cell = .Find(LookFor, LookIn:=xlValues, lookat:=xlPart)

Open in new window

with this:
Set Cell = .Find(LookFor, LookIn:=xlFormulas, lookat:=xlPart)

Open in new window

in your initial code.

You could also simply run a Replace on all cells as that will look in formulas automatically.
0
 

Author Comment

by:samprg
ID: 37058230
OK ,good  the macro does not change in the refrence.
but If I have that value in formula like   ="javascript:showLightbox('"&CURL("http://Yahoo.com")&"',1800,1800)"
the macro changes to javascript:showLightbox('"&CURL("http://Yahoo.com")&"',1800,1800)
converted it from formula to value
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37058386
I can't see why that would happen. Can you post a sample?
0
 

Author Comment

by:samprg
ID: 37060638
I attached test file, and You can test the macro
please watch D18 in ssheet DP, before run the macro is forumla, after run it, it be value

 Test.xlsm
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 37061887
The code in that file is not changed to what I suggested. It should be:
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:=xlFormulas, 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


The formula in D5 remains as it is.
0
 

Author Comment

by:samprg
ID: 37067717
Sorry, I uploaded the last version
please look at this Test.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37068232
D18 was a value to start with? D12 starts as a formula and remains one (though you have calculation set to manual so it doesn't update.)
0
 

Author Closing Comment

by:samprg
ID: 37071818
Thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 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