| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| make a check form, and then have info go to a check register | Excel Worksheet Functions | |||
| Copy and move check box (check boxes) with new cell link? | Excel Worksheet Functions | |||
| Increase size of a Forms Check Box (click on to enter check mark) | Excel Discussion (Misc queries) | |||
| Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
| Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
Linear Mode
