Loop through the results of Find / Add Hyperlink to all cells containing certain text

**js8765** · 04-13-2010, 07:21 AM

Hi there,

I was wondering if there is anyway to add a certain hyperlink to all cells where the entire cell contents match a certain value.

I know how to do this by looping through all sheets and cells, but my workbook has over 60 sheets and there are over 70 different values of cell contents that should have hyperlinks. Therefore I'm wondering if there is any quicker way.

One way I thought of was to do a find and replace the value with the hyperlink formula, but I am not able to do this. The text must remain the same in the cells.

Is there any way of looping through all the results of a find and gettting the address of each one?

Thanks in advance for any suggestions on how to complete this tricky task!

js

**StephenR** · 04-13-2010, 08:29 AM

Attach a small sample workbook including desired results, and somebody will help.

**js8765** · 04-13-2010, 09:10 AM

Hi,

So, here's the sample workbook.

As you can see, there is a sheet called "XYZ" and throughout the workbook there are cells whose entire contents are "XYZ". Everywhere I have such a cell, I would like a link placed to the sheet "XYZ" (like in Sheet1!B2)

The contraints are:
- I cannot use the formula "=Hyperlink(x,y)" in the cell and
- I would prefer not to have to loop through all cells in all sheets to find occurences of "XYZ" as the real workbook is huge and there I would have to loop through the entire workbook 40+ times to look for 40 different worksheet names.

I hope I've managed to explain this a bit better. Make sure to let me know if you want any more information.

Thanks again,

js

**StephenR** · 04-13-2010, 09:48 AM

This does XYZ, but if you multiple sheets, I think another loop would have to be built in to the code.

Please Login or Register  to view this content.

**js8765** · 04-13-2010, 09:56 AM

That's exacly what I was looking for.

Excellent reply.

Thanks very much!

Loop through the results of Find / Add Hyperlink to all cells containing certain text

LinkBack

Thread Tools

Rate This Thread

Display

Loop through the results of Find / Add Hyperlink to all cells containing certain text

Re: Loop through the results of Find / Add Hyperlink to all cells containing certain

Re: Loop through the results of Find / Add Hyperlink to all cells containing certain

Re: Loop through the results of Find / Add Hyperlink to all cells containing certain

Re: Loop through the results of Find / Add Hyperlink to all cells containing certain

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions