Excel Automation Find Method

jman · September 17th, 2003, 05:44 AM

Hello,

Please can anyone help me with the syntax? I have a value, and I need to find the correct cell in the spreadsheet which matches that value.

So far I have:

Dim mytarget As Range

Set objXL = CreateObject("EXCEL.Application")
Set myWB = objXL.Workbooks.Open("C:\testsheet.xls")
Set myWS = myWB.Sheets("Sheet1")
i = 1

StrDate = Right(strArrayNames((i), 1), 8)
' for testing only
StrDate = "12 March 2003"

    Set mytarget = myWS.Range("a1", "z10")
    Set mytarget = mytarget.Find(StrDate)
    ActiveCell(mytarget).Select

I don't think this is right, but I am unsure what to do to put it right.

Any help would be greatly appreciated

John

chrislepingwell · September 17th, 2003, 06:10 AM

The quick answer is to alter your code

Code:

ActiveCell(mytarget).Select

to

Code:

myRange.Select

you might also want to add some of the search options to your code i.e.

Code:

Set mytarget = mytarget.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte )

check VBA help for explanations of these, but I think you probably only need 'What', 'Lookin' and 'lookat'.

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know