Using Cells.Find etc... VBA

Gary_E

Board Regular
Joined
Feb 18, 2002
Messages
108
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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 ! :wink:

I will try to replicate your finding, but, I think this is very strange... :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top