I posted this a few days back. Have not yet figured out
how to fix it. Effectively, the routine simply carries
out queries on a spreadsheet and prints results which are
hyperlinks to cell addresses. The list of links can be
quite extensive. In some cases the list goes down to row
65,536. Each type of query is printed in a particular
column.

During the routine, my code seems to periodically
failing. The error is a 1004 Error. However I can't
figure out why this happening. It happens in row 65,532
or 64,420 etc. However it just failed in row 1,111. This
is the line of code it fails on. It fails after the If
statement on the the Errrng line:

If RowCheck(Errrng) = False Then

Errrng.Parent.Hyperlinks.Add Anchor:=Errrng, Address:="", _
SubAddress:=sStr1, _
TextToDisplay:=sStr

'Increment paste range for next comment
Set Errrng = Errrng.Offset(1, 0)
End If

When going to the immediate window I get:
?RowCheck(Errrng) = FALSE
True

?Errrng.Address
$F$1111

?sStr1
'Financing'!W37

?sStr
Financing!W37

Can anyone tell me what is happening here?

Hi ExcelMonkey,

When you go into Debug mode on an error, can you execute the Hyperlinks.Add
from the immediate window?

Errrng.Parent.Hyperlinks.Add Anchor:=Errrng, Address:="", SubAddress:=sStr1,
TextToDisplay:=sStr

Or do you get a 1004 there?

If you can do it in the immediate window, then it may be a timing issue. I
have run into this more times than I'd like - you can step through code just
fine, but when a routine is running at full speed, it intermittently errors
out. I've even had code that works perfectly on slow machines that fail on
faster machines due to timing issues. To see if that could be happening,
you can try adding a DoEvents right before the offending line of code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


ExcelMonkey wrote:
> I posted this a few days back. Have not yet figured out
> how to fix it. Effectively, the routine simply carries
> out queries on a spreadsheet and prints results which are
> hyperlinks to cell addresses. The list of links can be
> quite extensive. In some cases the list goes down to row
> 65,536. Each type of query is printed in a particular
> column.
>
> During the routine, my code seems to periodically
> failing. The error is a 1004 Error. However I can't
> figure out why this happening. It happens in row 65,532
> or 64,420 etc. However it just failed in row 1,111. This
> is the line of code it fails on. It fails after the If
> statement on the the Errrng line:
>
> If RowCheck(Errrng) = False Then
>
> Errrng.Parent.Hyperlinks.Add Anchor:=Errrng, Address:="", _
> SubAddress:=sStr1, _
> TextToDisplay:=sStr
>
> 'Increment paste range for next comment
> Set Errrng = Errrng.Offset(1, 0)
> End If
>
> When going to the immediate window I get:
> ?RowCheck(Errrng) = FALSE
> True
>
> ?Errrng.Address
> $F$1111
>
> ?sStr1
> 'Financing'!W37
>
> ?sStr
> Financing!W37
>
> Can anyone tell me what is happening here?

How do you execute within the immediate window? I tried:

? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
Address:="", _
SubAddress:=sStr1, _
TextToDisplay:=sStr

and recieved a compile error expected expression. Using
F8 does not work either to step through it after it has
failed

How exactly would I use the Do Event?

Thnks



>-----Original Message-----
>Hi ExcelMonkey,
>
>When you go into Debug mode on an error, can you execute
the Hyperlinks.Add
>from the immediate window?
>
>Errrng.Parent.Hyperlinks.Add Anchor:=Errrng, Address:="",
SubAddress:=sStr1,
>TextToDisplay:=sStr
>
>Or do you get a 1004 there?
>
>If you can do it in the immediate window, then it may be
a timing issue. I
>have run into this more times than I'd like - you can
step through code just
>fine, but when a routine is running at full speed, it
intermittently errors
>out. I've even had code that works perfectly on slow
machines that fail on
>faster machines due to timing issues. To see if that
could be happening,
>you can try adding a DoEvents right before the offending
line of code.
>
>--
>Regards,
>
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>
>[please keep replies in the newsgroup - email address
unmonitored]
>
>
>ExcelMonkey wrote:
>> I posted this a few days back. Have not yet figured out
>> how to fix it. Effectively, the routine simply carries
>> out queries on a spreadsheet and prints results which
are
>> hyperlinks to cell addresses. The list of links can be
>> quite extensive. In some cases the list goes down to
row
>> 65,536. Each type of query is printed in a particular
>> column.
>>
>> During the routine, my code seems to periodically
>> failing. The error is a 1004 Error. However I can't
>> figure out why this happening. It happens in row 65,532
>> or 64,420 etc. However it just failed in row 1,111.
This
>> is the line of code it fails on. It fails after the If
>> statement on the the Errrng line:
>>
>> If RowCheck(Errrng) = False Then
>>
>> Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
Address:="", _
>> SubAddress:=sStr1, _
>> TextToDisplay:=sStr
>>
>> 'Increment paste range for next comment
>> Set Errrng = Errrng.Offset(1, 0)
>> End If
>>
>> When going to the immediate window I get:
>> ?RowCheck(Errrng) = FALSE
>> True
>>
>> ?Errrng.Address
>> $F$1111
>>
>> ?sStr1
>> 'Financing'!W37
>>
>> ?sStr
>> Financing!W37
>>
>> Can anyone tell me what is happening here?
>
>.
>

Hi,

ExcelMonkey wrote:
> How do you execute within the immediate window? I tried:
>
> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
> Address:="", _
> SubAddress:=sStr1, _
> TextToDisplay:=sStr

It needs to be all on one line, and don't use the "?", which is a shortcut
for "Debug.Print" (you don't need a result in this case, you just want to
see if the statement executes without error).

> How exactly would I use the Do Event?

There is a command called "DoEvents". Just put it on a line by itself
before your Hyperlinks.Add line of code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

When I try to run it from the Immediate window I get a Run
Time Error 424 Object Required.


>-----Original Message-----
>Hi,
>
>ExcelMonkey wrote:
>> How do you execute within the immediate window? I
tried:
>>
>> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
>> Address:="", _
>> SubAddress:=sStr1, _
>> TextToDisplay:=sStr
>
>It needs to be all on one line, and don't use the "?",
which is a shortcut
>for "Debug.Print" (you don't need a result in this case,
you just want to
>see if the statement executes without error).
>
>> How exactly would I use the Do Event?
>
>There is a command called "DoEvents". Just put it on a
line by itself
>before your Hyperlinks.Add line of code.
>
>--
>Regards,
>
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>
>[please keep replies in the newsgroup - email address
unmonitored]
>
>.
>

Can't seem to find DoEvent in the Help database.

Typing DoEvent by itself creats an error. Is it a Do
While or Do Until loop that you are talking about?


>-----Original Message-----
>Hi,
>
>ExcelMonkey wrote:
>> How do you execute within the immediate window? I
tried:
>>
>> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
>> Address:="", _
>> SubAddress:=sStr1, _
>> TextToDisplay:=sStr
>
>It needs to be all on one line, and don't use the "?",
which is a shortcut
>for "Debug.Print" (you don't need a result in this case,
you just want to
>see if the statement executes without error).
>
>> How exactly would I use the Do Event?
>
>There is a command called "DoEvents". Just put it on a
line by itself
>before your Hyperlinks.Add line of code.
>
>--
>Regards,
>
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>
>[please keep replies in the newsgroup - email address
unmonitored]
>
>.
>

Sorry I found DoEvents in a book. Will try and get back
to you.


>-----Original Message-----
>Hi,
>
>ExcelMonkey wrote:
>> How do you execute within the immediate window? I
tried:
>>
>> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
>> Address:="", _
>> SubAddress:=sStr1, _
>> TextToDisplay:=sStr
>
>It needs to be all on one line, and don't use the "?",
which is a shortcut
>for "Debug.Print" (you don't need a result in this case,
you just want to
>see if the statement executes without error).
>
>> How exactly would I use the Do Event?
>
>There is a command called "DoEvents". Just put it on a
line by itself
>before your Hyperlinks.Add line of code.
>
>--
>Regards,
>
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>
>[please keep replies in the newsgroup - email address
unmonitored]
>
>.
>

DoEvents does not work. It failed on the same line of
code. Its funny because it pastes the item but just can't
seem to turn it into a hyperlink.

The routine steps through a loop and does several searches
based on criteria I set up. I can run 1 to 6 different
types of searches. When I run the first three it
populates the results of all three in columns side by
side. It fails on the third search type. Interesting,
the second search populates all 65,536 rows. When it
fails on the third if fails in column 1,111

However if I run just the third search it works fine. Its
almost as if amount of hyperlinks associated with the
second search results affects the third one when run back
to back.

do not understand why??????????


>-----Original Message-----
>Hi,
>
>ExcelMonkey wrote:
>> How do you execute within the immediate window? I
tried:
>>
>> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
>> Address:="", _
>> SubAddress:=sStr1, _
>> TextToDisplay:=sStr
>
>It needs to be all on one line, and don't use the "?",
which is a shortcut
>for "Debug.Print" (you don't need a result in this case,
you just want to
>see if the statement executes without error).
>
>> How exactly would I use the Do Event?
>
>There is a command called "DoEvents". Just put it on a
line by itself
>before your Hyperlinks.Add line of code.
>
>--
>Regards,
>
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>
>[please keep replies in the newsgroup - email address
unmonitored]
>
>.
>

Hi,

If the worksheet doesn't contain any sensitive data, I'd be willing to take
a look if you want to email it to me directly: mvp <-at-> longhead <--dot-->
com.

If the workbook is large, please zip it up before sending.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


ExcelMonkey wrote:
> DoEvents does not work. It failed on the same line of
> code. Its funny because it pastes the item but just can't
> seem to turn it into a hyperlink.
>
> The routine steps through a loop and does several searches
> based on criteria I set up. I can run 1 to 6 different
> types of searches. When I run the first three it
> populates the results of all three in columns side by
> side. It fails on the third search type. Interesting,
> the second search populates all 65,536 rows. When it
> fails on the third if fails in column 1,111
>
> However if I run just the third search it works fine. Its
> almost as if amount of hyperlinks associated with the
> second search results affects the third one when run back
> to back.
>
> do not understand why??????????
>
>
>> -----Original Message-----
>> Hi,
>>
>> ExcelMonkey wrote:
>>> How do you execute within the immediate window? I tried:
>>>
>>> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
>>> Address:="", _
>>> SubAddress:=sStr1, _
>>> TextToDisplay:=sStr
>>
>> It needs to be all on one line, and don't use the "?", which is a
>> shortcut for "Debug.Print" (you don't need a result in this case,
>> you just want to see if the statement executes without error).
>>
>>> How exactly would I use the Do Event?
>>
>> There is a command called "DoEvents". Just put it on a line by
>> itself before your Hyperlinks.Add line of code.
>>
>> --
>> Regards,
>>
>> Jake Marx
>> MS MVP - Excel
>> www.longhead.com
>>
>> [please keep replies in the newsgroup - email address unmonitored]
>>
>> .

Sounds good


>-----Original Message-----
>Hi,
>
>If the worksheet doesn't contain any sensitive data, I'd
be willing to take
>a look if you want to email it to me directly: mvp <-at->
longhead <--dot-->
>com.
>
>If the workbook is large, please zip it up before sending.
>
>--
>Regards,
>
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>
>[please keep replies in the newsgroup - email address
unmonitored]
>
>
>ExcelMonkey wrote:
>> DoEvents does not work. It failed on the same line of
>> code. Its funny because it pastes the item but just
can't
>> seem to turn it into a hyperlink.
>>
>> The routine steps through a loop and does several
searches
>> based on criteria I set up. I can run 1 to 6 different
>> types of searches. When I run the first three it
>> populates the results of all three in columns side by
>> side. It fails on the third search type. Interesting,
>> the second search populates all 65,536 rows. When it
>> fails on the third if fails in column 1,111
>>
>> However if I run just the third search it works fine.
Its
>> almost as if amount of hyperlinks associated with the
>> second search results affects the third one when run
back
>> to back.
>>
>> do not understand why??????????
>>
>>
>>> -----Original Message-----
>>> Hi,
>>>
>>> ExcelMonkey wrote:
>>>> How do you execute within the immediate window? I
tried:
>>>>
>>>> ? Errrng.Parent.Hyperlinks.Add Anchor:=Errrng,
>>>> Address:="", _
>>>> SubAddress:=sStr1, _
>>>> TextToDisplay:=sStr
>>>
>>> It needs to be all on one line, and don't use the "?",
which is a
>>> shortcut for "Debug.Print" (you don't need a result in
this case,
>>> you just want to see if the statement executes without
error).
>>>
>>>> How exactly would I use the Do Event?
>>>
>>> There is a command called "DoEvents". Just put it on
a line by
>>> itself before your Hyperlinks.Add line of code.
>>>
>>> --
>>> Regards,
>>>
>>> Jake Marx
>>> MS MVP - Excel
>>> www.longhead.com
>>>
>>> [please keep replies in the newsgroup - email address
unmonitored]
>>>
>>> .
>
>.
>