Using Cells.Find etc... VBA

[Gary_E]

Hi :

I have some code that looks for a very specific column header on a download file from the mainframe (column headers are only unique by 1 or 2 letters, account, account_roll, etc...). So I have to make sure, I selecting the proper column. Thus Use the Cell.Find (Download column structure isn't always).

Problem is after the macros run - The edit.find settings are lookat = xlwhole & matchcase:=True

Would love to find some code that would reset these defaults to the defaults lookat = xlPart and Matchcase:=False.

Below is how I'm planning to get around this one, but there must be a cleaner way.

Selection.Find(What:=string_acct, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate
string_b = ActiveCell.Address

''a little code to reset the lookat & matchcase
Selection.Find(What:=string_acct, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

[Juan Pablo González]

Why do you need to reset them ? Doesn't Excel do that automatically ? one example: VLOOKUP. The 4th argument (TRUE or FALSE) forces an Exact match.

So, if you use a formula like
=VLOOKUP(A1,B2:C5,2,FALSE)

and then, intend to use another VLOOKUP formula like
=VLOOKUP(A3,B2:C5,2)
expecting that Excel would "assume" the False, well, you're in for a big surprise !

I will try to replicate your finding, but, I think this is very strange...

[Gary_E]

Thxs for the note -
here try this, on a tab enter the following:
Enter in Cell A1 -> gary
Enter in Cell A3 -> gary_gary

Then copy the following macros in.
Run the sub find when done,
Try to use the toolbar : Edit-find what = gary_

won't work

Sub find()
Cells.find(What:="gary", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True).Activate
End Sub

[Ivan F Moala]

Gary
You are correct in this...the find function is one of those excel functions that holds
and saves its settings for the last options
selected or programed....there is no around this except to do what you have doen and that is to reprogram/reset these.....
I beleive the LookIn, LookAt, SearchOrder, are kept between calls.


Ivan

[Juan Pablo González]

Oh, I see... from Help File:

Remarks
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting

[ This Message was edited by: Juan Pablo G. on 2002-02-20 22:43 ]