PDA

View Full Version : Solved: Invalid cell references generated from macro



stixmcvix
03-29-2012, 07:05 AM
Hi there,

I used the fantastic VBA code provided on here vbaexpress.com/kb/getarticle.php?kb_id=195 for generating cell references to a text string. I have multiple worksheets, and the macro returns a long list of correct cell references with the search term in.

However..... clicking on the hyperlinks generates the error message: "Reference is not valid". If I hover over the hyperlink, the link looks fine to me, so for example for the cell text which reads: "APAC China Q4!A1943" the hyperlink is "file:///C:\VICTORIA\Market Insights Data\2011 ALL QUESTIONS GLOBALLY.xlsm - APAC China Q4!A1943" - but like I said, clicking it up kicks up the error message.

Does anyone know where I might going wrong.

For info, I have macro security set to enable macros and I am in Excel 2010.

Bob Phillips
03-29-2012, 08:15 AM
Post your workbook with your code, we are not going to track it all back.

stixmcvix
03-29-2012, 08:23 AM
Hopefully you should be able to see it.

Bob Phillips
03-29-2012, 10:17 AM
For Counter = 1 To UBound(FindCell)
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)
Next Counter

stixmcvix
03-30-2012, 02:06 AM
Thank you, problem solved!!!