Question

[eric]

I have the following Macro:

Sub ReplaceHyperlink()
'
' Macro to change Hyperlinks
'
Dim ws As Worksheet
Dim hyp As Hyperlink

For Each ws In ThisWorkbook.Worksheets
For Each hyp In ws.Hyperlinks
hyp.Address =
Replace$(Expression:=hyp.Address, Find:="PATH#1",
Replace:="PATH#2", Compare:=vbTextCompare)
Next hyp
Next ws
End Sub


We are changing server names and I got this macro to aid
users in changing some of their hyperlinks. I created it
in Excel 2002 and have a user using Excel 97. They get a
compiling error with the Replace$ function. Is this
function only available in 2002?

Please advise. Thanks for any assistance!

 

[Bob Kilmer]

VBA.Replace is new. See if you can't replace 'Replace' with
'Application.WorksheetFunction.Replace'. ;-)

Bob Kilmer

 

[Chip Pearson]

Bob,

The SUBSTITUTE worksheet function is the function that most
closely resembles VBA.Replace. The REPLACE worksheet function does
something else entirely.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Bob Kilmer]

True enough. Worksheet function REPLACE is more like VBA.Mid. Thanks Chip.