Help with find function please!

[Guest]

I am using VBScript to automate Excel through another program. I have written
some data to a row in the Excel worksheet. This part works perfectly.

I then need to look for a value in the row and get the address of the cell
where that value is located. The problem I am having is that the find
function is returning the wrong cell. When I look for the value 1, it will
return any cell in which the value contains a '1' such as cells with values
of 11, 21, or 111. Is there a parameter I need to change or a workaround for
this?

Here is a short sample of my code:
dim lookupval
dim myLoc

lookupval = 1

set myLoc =
objExcel.workbooks(1).worksheets(1).Range("A2:Z2").Find(CInt(lookupVal))
msgbox myLoc.column

 

[Fredrik Wahlgren]

I am using VBScript to automate Excel through another program. I have written
some data to a row in the Excel worksheet. This part works perfectly.

I then need to look for a value in the row and get the address of the cell
where that value is located. The problem I am having is that the find
function is returning the wrong cell. When I look for the value 1, it will
return any cell in which the value contains a '1' such as cells with values
of 11, 21, or 111. Is there a parameter I need to change or a workaround for
this?

Here is a short sample of my code:
dim lookupval
dim myLoc

lookupval = 1

set myLoc =
objExcel.workbooks(1).worksheets(1).Range("A2:Z2").Find(CInt(lookupVal))
msgbox myLoc.column

Isn't there an optional parameter called something like "Match entire cell"?
Check the documentation for the Find method.

/ Fredrik

 

[Guest]

There is a MatchCase parameter, but that merely deals with lower and
uppercase characters. There is a SearchFormat parameter, but I have no clue
what values it takes since I cannot find documentation for it anywhere. Why
does Microsoft not provide full documentation of constants and function
parameters?

I STILL NEED HELP!

 

[Fredrik Wahlgren]

There is a MatchCase parameter, but that merely deals with lower and
uppercase characters. There is a SearchFormat parameter, but I have no clue
what values it takes since I cannot find documentation for it anywhere. Why
does Microsoft not provide full documentation of constants and function
parameters?

I STILL NEED HELP!

:

Try to do what you want manually and record your steps. That should give
you the correct options

/Fredrik

 

[Guest]

I figured out the solution to my problem...the fourth parameter, "LookAt",
has an option called xlwhole (value is 1)...I had previously skipped over
this b/c I thought the xlWhole value was the default...it may or may not be,
I am still not sure, but my code worked when I explicitly set this value.....

line of code:
set myLoc =
objExcel.workbooks(1).worksheets(1).Range("A2:Z2").Find(CInt(lookupVal),,,1)

 

[Fredrik Wahlgren]

I figured out the solution to my problem...the fourth parameter, "LookAt",
has an option called xlwhole (value is 1)...I had previously skipped over
this b/c I thought the xlWhole value was the default...it may or may not be,
I am still not sure, but my code worked when I explicitly set this value.....

line of code:
set myLoc =
objExcel.workbooks(1).worksheets(1).Range("A2:Z2").Find(CInt(lookupVal),,,1)



:

That's the option I meant. Sorry, I have the Swedish version, otherwise I
would have given you the right name.

/Fredrik

 

[Tom Ogilvy]

There is no default. These correspond to the current setting in the
Edit=>Find dialog. It is always best to set them explicitly as you have
found out.