PDA

View Full Version : Solved: Get absolute address for hyperlinks



bassnsjp
04-15-2009, 10:06 AM
I'm using Excel 2003.
Have a macro that extracts all the hyperlinks and does a text search and replace. For those hyperlinks that do not contain the search string I write to a "StringNotFound" spreadsheet. However, if a spreadsheet contains hundreds or thousands of links that it would be tedious to locate exactly what cell the hyperlink is located in. So, what I would like to do is obtain the absolute cell location and provide that info in the "StringNotFound" along with contents of the hyperlink. How can I get the cell reference information in the form of A1, C24, etc for each cell that contains a hyperlink.

Dim h as Hyperlink
dim hypstr as String

For each h in Worksheets("XYZ").Hyperlinks

hypstr = h.address 'how do I get the cell location of h????
If replacestring does not exit in hypstr then write to StringNotFound

next

Kenneth Hobs
04-15-2009, 11:28 AM
Welcome to the forum!

Please put code between VBA tag codes. Press the VBA icon and paste between codes.

hypstr = h.Range.Address

mdmackillop
04-15-2009, 02:46 PM
Thanks for that one Ken, I've put it to good use already!
Regards
Malcolm

bassnsjp
04-15-2009, 07:05 PM
Thank you...I was very close with my attempts, but didn't get it exactly. Thank you again, that saved me alot of time and frustration.