Error 1004 on long For loop, adding hyperlinks. What's going on?
 

Greetings.

I have a very large worksheet with about 110,000 rows, and a VBA
program that analyzes each of these rows, comparing their values with
other sheets in the same workbook.

In one Sub in my program, I have a For loop that I expect to execute
once per row (about 110,000 times). Its job is to add a hyperlink to a
cell if the values in the row have a certain value:

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(52206, 14),
Address:="", SubAddress:="TargetWorksheet!A1"


The problem is that I get a "Run-time error '1004': Application-
defined or object-defined error" on iteration #52205 (the counter in
the For loop reads 52205). I've previously only gotten this error when
I incorrectly referred to an object. Any idea why this is happening?

Could I be exceeding the acceptable range of a Hyperlink anchor?

Thanks,
N. Lee

Error 1004 on long For loop, adding hyperlinks. What's going on?
 

On May 16, 9:29 am, N L wrote:
Greetings.

I have a very large worksheet with about 110,000 rows, and a VBA
program that analyzes each of these rows, comparing their values with
other sheets in the same workbook.

In one Sub in my program, I have a For loop that I expect to execute
once per row (about 110,000 times). Its job is to add a hyperlink to a
cell if the values in the row have a certain value:

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(52206, 14),
Address:="", SubAddress:="TargetWorksheet!A1"

The problem is that I get a "Run-time error '1004': Application-
defined or object-defined error" on iteration #52205 (the counter in
the For loop reads 52205). I've previously only gotten this error when
I incorrectly referred to an object. Any idea why this is happening?

Could I be exceeding the acceptable range of a Hyperlink anchor?

Thanks,
N. Lee

I've resolved the issue. Excel has a limit of 65,530 hyperlinks on a
single page. Instead of reporting this limit, VBA just gives a generic
error.
Shame on Excel on this shoddy behavior.

N. Lee