Thanks in advance,
---
Stavros P Zanos, MD
Dept of Nephrology
Papageorgiou General Hospital
Thessaloniki, Greece
Norman.
"Stavros P. Zanos" <sza...@otenet.gr> wrote in message
news:#1fD6esFCHA.2444@tkmsftngp05...
You could use Edit=>Replace and in the Find What box type company1 and
in the Replace With box type company2 then click on Replace All
--
Roger Govier
Technology 4 U W98SR2 XL2K
The rest will give you an idea of how you might set
up something that works, and how to obtain the
link that you can't readily see.
Something like this may better be suited if you are
changing things frequently.
Advantage: complete visibility of what you are doing.
A1: 'http://www.company2.com/
A2: 'link1.htm
B2: =HYPERLINK($A$1 & A2,"[x]")
You can find the underlying link with a userdefined
function, and then make the result a constant with
copy (ctrl+c), Edit, Paste Special, Values
Function HyperlinkAddress(cell)
'object type links only, not HYPERLINK worksheet functions
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function
You could write a macro to convert the underlying
links as follows this will just massively convert every
link on the page without regard to selection.
Color coded as follows
40 salmon tint, has link but was not changed
35 green tint, link and value changed (link matched value)
36 yellow tint, link changed, value restored
Disadvantage: can't see links, macro must match
Must specify exactly what links have as prefix http://www.abc
is not same as www.abc
i.e. you specified company2 without http:// prefix
this macro requires consistency
Sub MassLinkChange()
Dim hl As Variant
Dim rng1 As String
Dim OldLnk As String, NewLnk As String
Dim FromLnk As String, ToLnk As String
Dim OldLnkCell As String, OldLnkValue As String
FromLnk = http://www.company1.com/
ToLnk = "http://www.company2.com/"
For Each hl In ActiveSheet.Hyperlinks
OldLnkCell = hl.Parent.Address(0, 0)
OldLnkValue = Range(OldLnkCell).Value
OldLnk = hl.Address
Range(OldLnkCell).Interior.ColorIndex = 40 'unchanged
If UCase(Left(OldLnk, Len(FromLnk))) = UCase(FromLnk) Then
On Error Resume Next
NewLnk = ToLnk & Right(OldLnk, Len(OldLnk) - Len(FromLnk))
hl.Address = NewLnk
If UCase(OldLnkValue) <> UCase(OldLnk) Then
Range(OldLnkCell).Value = OldLnkValue
Range(OldLnkCell).Interior.ColorIndex = 36
Else 'changed link and changed display
Range(OldLnkCell).Interior.ColorIndex = 35
End If
On Error GoTo 0
End If
Next hl
End Sub
HTH, instructions to install see getstarted.htm on my site
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Stavros P. Zanos" <sza...@otenet.gr> wrote in message news:#1fD6esFCHA.2444@tkmsftngp05...
Using Edit\Replace to amend links certainly works for me .
If , for example, I amend your website link and then change it back (e.g.
edit\replace it with twit; edit\replace twit with it) I can then click on
the link and be transported(profitably!) to your web pages.
Regards,
Norman.
"David McRitchie" <dmcri...@msn.com> wrote in message
news:e6#fCptFCHA.2280@tkmsftngp12...
ctrl+h
replace abc.com
with mvps.org/dmcritchie/excel/colors.htm
B3: http://www.mvps.org/dmcritchie/excel/colors.htm
with old link of http://www.abc.com
previous testing wiped out link entirely that was
created via a macro.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Norman Jones" <norman...@btconnect.com> wrote ...
I typed in www.abc.com (and excel saw it as a hyperlink)
I dragged down a few cells
Selected that range and try to replace www.abc.com with www.microsoft.com.
The value in the cell showed www.microsoft.com, but the hyperlink tip showed
www.abc.com.
And when I clicked on the link, I didn't go to the microsoft.com site.
--
Dave Peterson
ec3...@msn.com
Unfortunately, my 'verification' test (amend the hyperlink using
edit/replace and then reverse the amendment using edit/replace) was
insufficiently rigorous. It appeared to work only because the hyperlink
remained unchanged despite its textual mutation.
Again, apologies.
Regards,
Norman.
"David McRitchie" <dmcri...@msn.com> wrote in message
news:enmwO9tFCHA.2636@tkmsftngp13...