Unable to get countif property of the worksheet function class

I keep getting the following error, when I run my macro:

Runtime error '1004'
Unable to get countif property of the worksheetfunction class

Please Login or Register  to view this content.

The macro searches all sheets for external links, and records unique entries the linklist worksheet. (original function from an online example I cant recall where).

It works great where there is only 1 link in a cell. However it trips up when there are for instance link+link in the cell formula. It dies at the countif line with the above error.

Does anyone have any idea why? While I'd prefer to leave the countif line in to remove duplicate entries, there wont be too many of these types of values in the workbooks anyway, so I tried to have it just skip the 'check if already listed' part if this error occured and just add the link to the list anyway (to be sorted out manually later) but i cant seem to make an error handler that works either.

Ideas experts?!

I found a work around for the problem above by extracting my link from the left hand side of the string instead of the right. I could now extract the 2nd, 3rd, 4th link in a cell quite easily (as long as non of the linked cells are ranges!), by counting where each : is and trunkating the string at that value.

The problem now is this is becoming a massive proccessor drain with all the loops. I'm lucky enough to be on a top spec core2duo machine, but even so it takess a good 3 mins of locked up pc to generate the results. Thats without extracting the multiple links in the cells.

Is there anyway to speed this up?:

Please Login or Register  to view this content.