Results 1 to 5 of 5

Using Cells.Find etc... VBA

This is a discussion on Using Cells.Find etc... VBA within the Excel Questions forums, part of the Question Forums category; Hi : I have some code that looks for a very specific column header on a download file from the ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Charlotte, NC USA
    Posts
    108

    Default

    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

    Thxs for tips - this place is a great source for tips & tricks

    Gary

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default

    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...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Charlotte, NC USA
    Posts
    108

    Default

    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

    Thxs for tips - this place is a great source for tips & tricks

    Gary

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default

    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 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com