PDA

View Full Version : Solved: Why It doesn't find all hyperlinks?



blis
02-21-2010, 09:55 AM
Hello again.

I have macro which copies all hyperlinks in a workbook to newly inserted worksheet "SeznamHyperlink". It works without a problem.
Now i am trying to replace each hyperlink with function. And here is my problem. When i add code that clear contect of active cell, macro is not able to find all the hyperlinks in a worksheet. It finds first hyperlink, but then it doesn't jump to next one but to the 4th one. Also suddenly macro is not able to go to the next worksheet in a workbook. It just finds a few hypelinks in the first worksheet (and also clear the content of cellls and inserts hyperlink function)
Can anyone figure why is this happening. where is a mistake in my code, that is responsible for this jumps and problem when changing worksheet??

Here is the link to workbook http://dc180.4shared.com/download/22...lmost_beta.xls (http://dc180.4shared.com/download/226749179/79342777/Katalog_almost_beta.xls)

The macro has some blocked rows of code at the end. If you run macro with this rows blocked, macro works. It lists all hyperlink found on new sheet. When the macro is ran with this blocked rows, then it doesn't find all the hyperlinks and can't change active sheet.

Pls someone help me with this one.

lucas
02-21-2010, 10:20 AM
You really need to use Option Explicit at the very top of your modules, all of them.

If I were you, I would try to get the commented code working in a seperate module first.

blis
02-21-2010, 10:50 AM
I putted "Option Explicit", and my variables are fine. I didn't misstyped and they are all declered. This was preaty good guess thou. I can be sometimes a bit quick and misstype or forget someting.
Any other ideas?

Paul_Hossler
02-21-2010, 12:01 PM
when deleting things like that, I've found it's always safer to start at the bottom and go to the top


For h_link = ws.UsedRange.Hyperlinks.Count To 1 Step -1


See if that helps

I could run the macro fine, but am not sure about the results

Paul

blis
02-21-2010, 02:21 PM
The problem acures when I am trying to delete cell content (hyperlink) :

ActiveCell.ClearContents

here is new link to workbook Katalog_almost_beta.xls (http://www.4shared.com/file/226931287/56cc3d69/Katalog_almost_beta.html)


If I block this row, the macro runs almost perfect (see linked workbook). It replaces all hyperlinks with function "=hyperlink("appropriet cell with address";"appropriet cell with friendly name").
But actually in cells are still inserted hyprelinks and not functions (u can change refference but the hyperlink function is still pointing to the same address or bookmark). This could be solved if the cell content would be cleared before inserting function hyperlink.

Is there any other way to clear cell content? If I use "ActiveCell.ClearContents "then macro goes insane.
It works fine for firs hyperlink found, but then it jumps to te 4th and then 6th, 8th, 10th and 12th hyperlink in a workbook.
So this jumps are not random, it jumps to even numbers of hyperlinks (with exception of hyperlink no2).

Anyone understands me at all?

blis
02-21-2010, 02:29 PM
Paul u are the man!!!!

Thank you mio times for this. your code did the trick :)))) now i can go to sleep. Thanks to you!

If u have time, could u write short explanation of that code.

Thank you again.

blis
02-21-2010, 02:50 PM
I have looked code a bit better and now I understand what u ment by going back :) this is really something... going the outher way solves a problem lol

Thx again

Paul_Hossler
02-21-2010, 05:12 PM
Glad that was it was

Paul