Check for Hyperlink

[Mike Fogleman]

I have a column of data that has a hyperlink on some rows, but not all. I
need to loop through say column B and if the cell has a Hyperlink then
change it to a new one. In this column the basic change would be the URL IP
& CMTS IP.
Old
http://12.220.6.163/cgi-bin/Service...2.220.5.233&SITE_ID=2&LIBRARY_NAME=laflivfile

New
http://12.220.9.41/cgi-bin/Service_...2.220.5.241&SITE_ID=2&LIBRARY_NAME=laflivfile

As you can see they have changed the site IP and the CMTS IP. Everything
else remained the same. These two items would need changed for every cell in
column B that has a hyperlink. However, each row in column B will have a
different NODE name. For example the next cell that has a hyperlink would
have:
http://12.220.6.163/cgi-bin/Service...2.220.5.233&SITE_ID=2&LIBRARY_NAME=laflivfile

Notice the Node name in the middle of the link is different.

NODE_STATUS_REQUEST&NODE=E-03
This needs NOT to be changed for that cell.

How would I test for a hyperlink and if it has one, change only the 2 IPs?
I am thinking a helper sheet would be handy for future changes where I enter
the new IPs in A1 & A2 and use those values in the code. Any suggestions or
code would be appreciated to get me started.

Mike F

 

[Dave Peterson]

The hyperlinks that were inserted via Insert|Hyperlink?

Option Explicit
Sub FixHyperlinks()
Dim OldStr As String
Dim NewStr As String
OldStr = "12.220.5.233"
NewStr = "12.220.5.241"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Range("b:b").Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

This code was is a modified version of David McRitchie's:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

 

[Mike Fogleman]

Thanks Dave. I used this format and modified it for several different
columns and also created hyperlinks where they were missing.

Mike F

 

[Dave Peterson]

When I have lots of hyperlinks on a worksheet, I'll use:
=hyperlink()

I find them to be better behaved.