Help with find function please!
 

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

Help with find function please!
 

"crashoverride" wrote in message
...
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

Help with find function please!
 

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" wrote:


"crashoverride" wrote in message
...
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

Help with find function please!
 

"crashoverride" wrote in message
...
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" wrote:

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

/Fredrik

Help with find function please!
 

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" wrote:


"crashoverride" wrote in message
...
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" wrote:

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

/Fredrik

Help with find function please!
 

"crashoverride" wrote in message
...
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" wrote:


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

/Fredrik

Help with find function please!
 

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.

--
Regards,
Tom Ogilvy


"crashoverride" wrote in message
...
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" wrote:


"crashoverride" wrote in
message
...
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" wrote:

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

/Fredrik