EXPERT CHALLENGE: HYPERLINK NOT WORKING?

[HI005P]

The attached XLS uses a VBA module "SEARCH" (DoFindAll) that allows you to find a key word in Excel. The search results are returned to a new worksheet named "FindWord". The results are listed with hyperlinks pointing back to the cell(s) the text appears in. The search process works fine except for the hyperlinks. The search results are shown in blue and underlined as if they have been assigned a hyperlink, but when you click on the link an error pops up with "Reference is not valid." Can you suggest what adjustments are needed in order for the hyperlinks to work correctly .

Thank you very much for your assistance.

ps

See attached file SearchWord_Test on reply below.

[HI005P]

Please see attached file.

[Andy Pope]

The problem is the spaces in your sheet names. You need to enclose the name in single quotes.

[vba]
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & Counter + 2), _
Address:="", SubAddress:=FindSheet(Counter) & "!" & FindCell(Counter), _
TextToDisplay:="'" & FindSheet(Counter) & "'!" & FindCell(Counter)
Range("B" & Counter + 2).Value = FindText(Counter)
[/vba]

[HI005P]

Andy,

Thank you. Problem solved. The solution wasn't single quotes on the worksheet name under TestToDisplay:=, but under SubAddress:=. However, I suspect you already knew that but wanted me to discover it.

Brilliant!!!

Here's the fix...

ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & Counter + 2), _
Address:="", SubAddress:="'" & FindSheet(Counter) & "'!" & FindCell(Counter), _
TextToDisplay:=FindSheet(Counter) & "!" & FindCell(Counter)
Range("B" & Counter + 2).Value = FindText(Counter)

[Andy Pope]

sorry about that. But glad you managed to fix my typo.

[HI005P]

you da man...

Thanks again.