Is there a fast find when using excel?

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!

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

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:

VB Code:

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).

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?

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.

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:

VB 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:

VB Code:

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

you could do this:

VB Code:

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

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

Quote:

---

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

---

What about

VB Code:

xlSheet.Columns(1).Select