Application Error 1004

[ExcelMonkey]

I am running a routine which checks every cell in my model
and based on certain search criteria pastes the results
(cell address) in a summary sheet in column B.

The routine works well until I use a very large model.
The code generates an application error 1004. In this
instance, the routine has pasted cell address all the way
down to row 65,532 of column B in the summary sheet.

I am wondering if this error is being generated due to the
fact that I have 65,532 cells with data (i.e. memory
issues). I do not het have error handling in that puts an
Excel row constraint (65,536)into the paste routine

Can anyone tell me what is going on and how to get around
this?

 

[ExcelMonkey]

Further to my question, the line of code that is failing
on is:

'Paste address into sheet
Hardrng.Parent.Hyperlinks.Add Anchor:=Hardrng,
Address:="", _
SubAddress:=sStr1, _
TextToDisplay:=sStr

Yet it works fine until this point in the routine.

 

[Dave Peterson]

I would think that the cell you're plopping your hyperlink into is past row
65536.

I think I'd try to check that before adding the hyperlink.

I'm not sure how you get HardRng, but...

with activesheet
if hardrng.row > .rows.count then
set hardrng = .cells(1,hardrng.column+1)
end if
end with

==
But if you go over 256 columns (by 65536 rows), you're still in trouble! It'll
be time to add a new worksheet.

 

[ExcelMonkey]

Thats whats funny here is that the code stops on row
65,532 not 65,536. It stops 4 rows before the last row.
Effectively the code is going throught the UsedRanges of
each sheet and searching for cells based on search
criteria. Upon finding them, the routine pastes their
addresses as hyperlinks into cells. So I am not sure why
it would run properly all the way to 65,532 and then
fail. I could always put in a contraint that stops it
early at 65,300. But this isn't really solving the
problem.

 

[Dave Peterson]

I didn't see anything in your post that would explain the problem--but I didn't
fill up a worksheet with 65k rows of data and see what happened either.