.Hyperlinks.Add Error Midway through Routine

Hello, I have been staring at this code for over a day now and cannot figure out why it works great for over 60K times and then it throws an error when I get to the 60K+ iteration.

I am running a routine that is performing quality control checks on a data set that is over 600,000 cells. It checks various things, and when it finds an error it writes the error to the sheet that is logging errors. One of the things it does is writes a hyperlink to the cell where the data was found.

It gets through almost all of 3865 rows of data being inspected and then it fails on very last column of the data set (column 175). See below image.

QC Report Fail Point.PNG

The error I am getting is: "1004 | Application-defined or object-defined error"

I have isolated the error to the following code area:

Please Login or Register  to view this content.

I have checked the variables and they are all defined and present. When it gets to this point the .hyperlinks.add portion throws the above-noted error.

The data being evaluated is no different at the point it fails then a hundred other cells it evaluates perfectly fine. see below image.

QC Report Fail Point - Source Data.PNG

I appreciate the help. I have run out of ideas to try to resolve the error.

There is an intrinsic limit of 66,530 hyperlinks allowed in one worksheet. Do you know how many you have inserted by the time it fails?

Jeff,

That is my problem! Do you know of any work arounds?

It is failing on the 66531 th hyperlink. For hours I thought I had variable integer problem, but could not find it. Now I know it was with the limit you mention above. Now I have to try to find a way to work around the limitation! Your insight, advise, guidance is much appreciated!

I found part of the problem.

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

However, the code

Please Login or Register  to view this content.

is producing an error as well.