Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Curious Find/FindNext Behaviour
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? -- HTH... Jim Thomlinson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Curious Find/FindNext Behaviour
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. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: 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? -- HTH... Jim Thomlinson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Curious Find/FindNext Behaviour
This code runs prior to running any query. The Find code is used to create
souce data (Recordset) for the update or append. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: 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? -- HTH... Jim Thomlinson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Curious Find/FindNext Behaviour
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. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: This code runs prior to running any query. The Find code is used to create souce data (Recordset) for the update or append. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: 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? -- HTH... Jim Thomlinson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Curious Find/FindNext Behaviour
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 "Jim Thomlinson" wrote in message ... 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? -- HTH... Jim Thomlinson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Find & FindNext in a form | Excel Programming | |||
Using 'Find' and 'FindNext' in vba | Excel Programming | |||
Find...FindNext Problem | Excel Programming | |||
curious sheet behaviour | Excel Programming |