Excel searching and VB

[kl34i2]

I'm currently developing some software that accepts a list of words from a file and then searches all matching excel files found in a given folder. The issue i've run into is that searching a large spreadsheet (A1-N64000) seems to progressively become slower. This particular problem can be duplicated by opening this large spreadsheet and searching for a term that is found on the 64000th row. When clicking "Find Next" the box that has the current cell updates quickly and then starts updating every 100 rows or so, eventually diminishing to about 20 rows. I've tried setting all excel properties (screen updating, calculation, alerts, hiding, etc...) but none of them seem to work. Does anyone have an efficient method of searching an excel workbook programmatically? I'm using XP with Office 2003.

Thanks in advance.

[mark007]

If it's slow doing it with control F I can't see that there's anyway of speeding it up unfortunately. You could look into the text file structure of the file and see if all the cell data is unencrypted and intact. If so this may be an option...

[kl34i2]

I've come to determine that the argument Lookin:=xlValues is what's causing it to hang...when I remove it, the speed is amazing, when I insert it, I start experiencing delays...the most I've waited is 40 minutes for a find to complete before killing excel...what is the correct find syntac when trying to find a value in both xlValues and xlFormulas...not in a macro though because I've read that the values defined for xlValues and xlFormulas might be different than those defined in VB6. Thanks for the reply though.

btw...this is what I'm using:

[vba]Set oUsedRange = oSheet.UsedRange
Set oFoundResults = oUsedRange.Find(What:=searchTerm, _
LookIn:=xlValues, MatchCase:=False, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)[/vba]

the bolded piece above is the key to this problem...

[macleanb]

Hi

Not just to confuse you honest - I created a test spreadhseet with a jumble of "asdasd" type strings (rubbish - about 200 variations) and filled a spreadsheet A1:O65536 with this data. When I search this performance seems pretty linear and finding a string that was unique to the last row took ~ 4 seconds. I am suspecting that this is not a feature of search, but something a bit more environmental. SO I am thinking you should widen your search somewhat and perhaps take a look at the performance tab of task manager while you are doing this. I have a suspicion that you must be paging - as this fits the "performance cliff" you are falling off. Perhaps I have missed a salient step to recreate this issue - please let me know.

[Ken Puls]

Hi kl34i2,

Just an FYI, I edited your post to use the VBA tags developed for this site by our very own Mark007. I also put in some line continuation characters so your text wrapped in the VBA tags.

If you'd like to use them in future, just use (vba) VBA code here (/vba), where the brackets are replaced by [ and ].

Cheers,