Curious Find/FindNext Behaviour

[Jim Thomlinson]

In short my question is this... Is there some kind of issue using FindNext in
the Before_Save Event?

Here is what I am up to. I have a spreadsheet being used remotely by a bunch
of end users. It updates a DB2 database here at head office via ADODB
recordsets. The trigger that I am using to send the data is that the data is
sent any time the user saves their spreadsheet. The spreadsheet determines
what data needs to be appended or updated in the database using some formulas
which flag the items with the word Update or Append. That is pretty straight
forward stuff. Here is what is happening that is causing me some concern
though. If I call the write procedure from the Before_Save Event in
ThisWorkbook it has a problem with this code...

Private Function FoundRange(ByVal strInput As String, ByVal rngToSearch As
Range) As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngFound = rngToSearch.Find(What:=strInput, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If rngFound Is Nothing Then
Set FoundRange = Nothing
Else
Set FoundRange = rngFound
strFirstAddress = rngFound.Address
Do
Set FoundRange = Union(FoundRange, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Function

It finds the first item and sets rngFound correctly, but when it proceeds to
the FindNext, rngFound is set to nothing (What the Heck!). If I call the
write procedure not from the before save event it works great. Is there some
kind of issue using FindNext in the Before_Save Event?

 

[Tom Ogilvy]

A wild guess, but is the Backgroundquery parameter set to true on your query.

If so, maybe the query hasn't completed when the code runs. If appropriate,
try setting it to true.

 

[Jim Thomlinson]

This code runs prior to running any query. The Find code is used to create
souce data (Recordset) for the update or append.

 

[Jim Thomlinson]

Well I am running out of good reasons why this might be blowing up on me. I
am going to go looking for any connections or recordsets that I may have left
open but I just might have to chalk this one up to "The Darndest Thing"...
Thanks to everyone who read and pondered my problem.

 

[Peter T]

Hi Jim,

Your Find function worked fine for me when called in the Before_Save Event,
albeit with nothing else going on. Providing your variables are not being
reinitialized maybe this might work -

Private Function GetFindRange(ByVal strInput As String, _
ByVal rngToSearch As Range, _
ByRef rngFound) As Long
Dim cnt As Long, i As Long
Dim strAddress As String

ReDim asAddr(1 To 100) As String

Set rngFound = rngToSearch.Find(What:=strInput, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)

If Not rngFound Is Nothing Then

strAddress = rngFound.Address

Do
cnt = cnt + 1

If cnt > UBound(asAddr) Then
ReDim Preserve asAddr(1 To UBound(asAddr) + 100)
End If

asAddr(cnt) = strAddress

Set rngFound = rngToSearch.FindNext(rngFound)

strAddress = rngFound.Address

Loop Until strAddress = asAddr(1)


With rngToSearch.Parent
Set rngFound = .Range(asAddr(1))
If cnt > 1 Then
For i = 2 To cnt
Set rngFound = Union(rngFound, .Range(asAddr(i)))
Next
End If
End With

End If

GetFindRange = cnt

End Function

Note rngFound is passed ByRef, I don't like returning objects to functions,
but you could of course do that. Perhaps passing back the array of addresses
might serve your needs.

For my purposes I do the union elsewhere and cater for the possibility of
very many areas which is slow with Union.

But it seems your problem was this line -

Set rngFound = rngToSearch.FindNext(rngFound)

- which still exists in this revised function so not optimistic, unless the
problem stemmed from the Union.

Regards,
Peter T