Check for Hyperlink
 

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_...E=laf livfile

New
http://12.220.9.41/cgi-bin/Service_V...E=laf livfile

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_...E=laf livfile

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

Check for Hyperlink
 

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 wrote:

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_...E=laf livfile

New
http://12.220.9.41/cgi-bin/Service_V...E=laf livfile

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_...E=laf livfile

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

Check for Hyperlink
 

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" wrote in message
...
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 wrote:

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_...E=laf livfile

New
http://12.220.9.41/cgi-bin/Service_V...E=laf livfile

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_...E=laf livfile

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

Check for Hyperlink
 

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

I find them to be better behaved.

Mike Fogleman wrote:

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" wrote in message
...
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 wrote:

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_...E=laf livfile

New
http://12.220.9.41/cgi-bin/Service_V...E=laf livfile

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_...E=laf livfile

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

--

Dave Peterson