Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Function | Excel Worksheet Functions | |||
Need to find a function. | Excel Worksheet Functions | |||
Find Function | Excel Discussion (Misc queries) | |||
FIND function? | Excel Discussion (Misc queries) | |||
backwards find function to find character in a string of text | Excel Programming |