Is there a fast find when using excel?

[Cole Patzman]

I have a problem in trying to perform a fast find in excel. If there is such a thing.

Background, I have an excel spreadsheet with 30,000 records, on each of the 9 worksheets. I have a set of 79 values that I want to find.

I recorded a macro, which consisted of "Finding" a specific value, in Column A which this is the code for:

Columns("A:A").Select
Selection.Find(What:="1234", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

What syntax do I have to use for the find to work in VB?
The following is the code for opening the Workbook

xl_name = "c:\boe\DVAR_all.xls"
Set xl = GetObject(xl_name)
xl.Application.WindowState = xlMaximized


Any help would be greatly appreciated!

[davebat]

macros are written in VBA so it should be the same. But im no expert

[si_the_geek]

It is essentially the same, but with an important modification.

Every method/object related to the application/workbook/worksheet needs to be preceded by an appropriate object, eg:

xl_name = "c:\boe\DVAR_all.xls"
Set xl = GetObject(xl_name)
xl.Application.WindowState = xlMaximized
 
Dim xlBook as Object  'setup objects
Dim xlSheet as Object
Set xlBook = xl.WorkBooks(1)
Set xlSheet = xlBook.WorkSheets(1)
 
[b]xlSheet.[/b]Columns("A:A").Select
[b]xl.Selection.[/b]Find(What:="1234", After:=[b]xlSheet.[/b]ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:=False).Activate
...

Oh, and if you havent got a reference to Excel (in project-references) you will need to define all the constants that you use (such as xlByRows).

[Cole Patzman]

Si, Cut and pasted your code in the beginning of the procedure. when Set xlBook = xl.Workbooks(1) was read, got a 438 runtime error. "Object doesn't support this property or method". I do have the Excel 9 reference checked. Any ideas?

[Cole Patzman]

I have got past the 438 runtime, changed the suggested code to the following:

Dim xlBook As Object 'setup objects
Dim xlSheet As Object
Set xlBook = xl.Application.Workbooks(1)
Set xlSheet = xlBook.Worksheets(1)

when the next command is read, I get a Run-time error of 1004,
Select method od range class failed.


xlSheet.Columns("A:A").Select 'does not like this command in the least.

[si_the_geek]

Ah ok, I see that the GetObject actually gets the Workbook rather than the application. In that case you can use this as the initial code:

xl_name = "c:\boe\DVAR_all.xls"
 
Dim xlBook as Object  'setup objects
Set xlBook = GetObject(xl_name)
xlBook.Application.WindowState = xlMaximized
 
Dim xlSheet as Object
Set xlSheet = xlBook.WorkSheets(1)

As for the Selection issue, I recommend avoiding Selection if possible, so rather than this:

xlSheet.Columns("A:A").Select
xl.Selection.Find(What:= .... )

you could do this:

xlSheet.Columns("A:A").Find(What:= .... )

if that doesnt work, add this just before the line with the error:
xlSheet.Activate
xlSheet.Select

[Dave Sell]

Originally posted by Cole Patzman
xlSheet.Columns("A:A").Select 'does not like this command in the least.

What about

xlSheet.Columns(1).Select