+ Reply to Thread
Results 1 to 5 of 5

Problem with serach function CTRL+F

  1. #1
    Registered User
    Join Date
    05-14-2020
    Location
    Mauritius
    MS-Off Ver
    Office 365
    Posts
    74

    Problem with serach function CTRL+F

    Hello together,

    I hope someone can help me with this issue. Explanation:

    The user in our company are using several Excel files (xlsx) where they have to search for values using CTRL+F. All good. The search settings are normal like this
    Capture1.JPG

    Now they have to use a macro enabled file same time as a kind of tracker for their work with some code to automate some things in there.
    Now when they are using both files parallel then a strange change is happening. The search setting change:
    Capture2.JPG

    Suddenly automatically the box for "Match entire cell contents" is ticked and they cannot search for partly words etc. Always they have to remove the tick first. That's a bit annoying. They didn't encountered this issue before they had to use the .xlsm file.

    Can someone help me and explain why this is happening? And how i am able to change this?

    Thank you in advance.
    Greetings

    Tor


  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,482

    Re: Problem with serach function CTRL+F

    Share the VBA code.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    7,739

    Re: Problem with serach function CTRL+F

    Excel "remembers" the settings used in find & replace, regardless of whether it's done manually, or via a macro.
    There is no realistic way of finding what the settings are at the start of a macro & resetting them after the macro has run.

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,846

    Re: Problem with serach function CTRL+F

    Hi MRUTOR,

    When you open up an Excel Application, the .find parameters start with a Clean Slate.

    According to Microsoft Documentation https://docs.microsoft.com/en-us/off...cel.range.find
    the only sticky items are LookIn, LookAt, SearchOrder, and MatchByte

    However, I have found that several other items are also 'sticky'. They are 'sticky' (both manually and with VBA) until the Excel Application (not file) closes.

    If you want to restore .find to it's original state for manual operation, it is the VBA user's responsibility to restore the original .find parameters. This can be done with a dummy .find call.

    Example - Sticky .find:
    Please Login or Register  to view this content.


    Example - UnSticky .find:
    Please Login or Register  to view this content.
    Lewis

  5. #5
    Registered User
    Join Date
    05-14-2020
    Location
    Mauritius
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Problem with serach function CTRL+F

    Dear Lewis,

    thank you very much. this was the solution. Never thought about this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Disable Ctrl+A, Ctrl+C and Ctrl+V in Excel
    By wedzmer in forum Excel General
    Replies: 5
    Last Post: 05-10-2018, 03:59 AM
  2. Disable cut,copy,paste(including ctrl x, ctrl c n ctrl v)
    By Ramboexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2014, 01:00 AM
  3. ctrl+break problem!
    By kmlprtsngh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-04-2010, 06:32 AM
  4. VBA Ctrl + A Problem
    By succel in forum Excel General
    Replies: 1
    Last Post: 08-05-2010, 01:41 PM
  5. Replacement Problem (Ctrl+H)
    By keithfender in forum Excel General
    Replies: 6
    Last Post: 05-27-2009, 11:41 PM
  6. Ctrl+Shift Selection Problem
    By Tibbs in forum Excel General
    Replies: 7
    Last Post: 02-09-2009, 09:22 AM
  7. Ctrl-F3 problem
    By pats.pats.pats@gmail.com in forum Excel General
    Replies: 1
    Last Post: 02-26-2005, 01:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1