Help Needed - Run Macro on multiple cells

[rwilson91]

Hi,

I have a Excel file with several hundred hyperlinks, and excel (2010) decided to crash and changed the beginning of all my hyperlinks from "Y:\" to "..\"
I don't know why and can't figure it out. it has done it before but that was due to the location of the auto save folder. regardless i don't know why.

I'm now faced with the task for changing 600-900+ hyperlinks one at time.

I found a similar post online with the following macro to try fix the problem;

Please Login or Register  to view this content.

It seems to work but it still requires me to select each cell individually and run the macro, even with a shortcut that is going to take a LONG time.

Please can you advise a new or improved method to fix the problem.

Many thanks in advance.
Ross

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[alansidman]

Try this solution:

https://support.microsoft.com/en-us/kb/247507

[rwilson91]

Hi Alan,

Tried that, it seems to work same as the previous code, don't know if i'm doing something wrong, but it doesn't seem to be working as it's saying it should.

I'll attach my excel file so you can take a look if you have time.
(I have been trying to restore the links on the most recent tab, Jan15)

DUE DATES - Copy.xls

[alansidman]

In your sample worksheet, I do not find a Jan15 sheet and I don't see any hyperlinks that resemble the old string as shown in your module in the file. Am I missing something here?


Edit: Found the Hyperlinks. But are we working on Jan 16 and not Jan 15?

[alansidman]

I think I found it. Your slashes are in the wrong direction in your code. Change them to forward "/" slashes and then give it a go. I think that will make it ok.

[rwilson91]

Hi Alan,

I meant jan16, not jan15, sorry about that.

I've changed the code as suggested, but little confused, when i view the hyperlink clicking "edit hyperlink" it show the slashes as backslashes. Regardless I changed the code to following

Please Login or Register  to view this content.

It appears to be working now, clicking anyway on the worksheet and running the macro seems to correct all the hyperlinks, sheet i then need to do this on each work sheet (i can deal with that, no problem).

Thanks Alan for your help.
I'll be sure to come back next i have a problem, hopefully not in the near future.