+ Reply to Thread
Results 1 to 8 of 8

Modify Search Macro to also find upper case

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    dd
    MS-Off Ver
    Excel 2003
    Posts
    4

    Talking Modify Search Macro to also find upper case

    Hi All

    I use the below macro which works extremely well (where the variable cFnd is set to a cell with my search criteria)... is there any way to modify it to also find words where the first letter is capitalised (or ignore letter case altogether)

    i.e. now if I search "hello" it will not highlight text with "Hello" - I would like it to highlight both "hello" and "Hello"

    At the moment I have to run it twice with the cFnd variable modified... but that makes it very slow !

    Please Login or Register  to view this content.
    Any help gratefully received !

    Best
    James

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Modify Search Macro to also find upper case

    The usual way to do this is to convert the stuff being compared to being the same case.

    e.g.

    cFnd = LCase(cFnd)

    WIth both sides of an If statement converted in that way you don't need to worry what the original data looks like. "Hello' will be returned as "hello", as will "HeLLo" or "heLLO"...etc.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-31-2013
    Location
    dd
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Modify Search Macro to also find upper case

    Thanks for the quick response Richard, that's very kind.

    Maybe I am not not correctly understanding, but my constraint is that unfortunately, I'm not able to change the formatting of the text I am searching... so whilst I can run the macro twice (searching for "hello" once and "Hello" the second time), what I cant do is change the text being searched to always be "hello"...

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Modify Search Macro to also find upper case

    I'm not saying you need to change the text at all. I'm saying when the macro picks up the text as a VBA variable, its the variable that becomes lower case.

    Upload the workbook so that we can see the request in context and understand what's in the code as it processes.

  5. #5
    Registered User
    Join Date
    07-31-2013
    Location
    dd
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Modify Search Macro to also find upper case

    Hi Richard

    Thanks - I had a feeling I might have misunderstood your suggestion - though I confess Im still struggling to understand the code modification I need to do.

    As per your suggestion, attached a sample of the sheet (forgive the random data Ive put in from Pride & Prejudice!)

    If you try searching for "Long" or "long", you will see that only the "long"s get highlighted !

    Thanks again for your help

    James
    Attached Files Attached Files

  6. #6
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Modify Search Macro to also find upper case

    Just add 1 line above your code.

    Please Login or Register  to view this content.
    多么想要告诉你 我好喜欢你

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Modify Search Macro to also find upper case

    Split Function
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-31-2013
    Location
    dd
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Modify Search Macro to also find upper case

    Thanks so much millz & jindon - I tested both these solutions, and both work really well !

    Very much appreciated for your help!

    Best
    James

+ 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. Make macro work with upper case and lower case
    By Caitlin535 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-06-2019, 01:19 PM
  2. [SOLVED] Find and replace Lower case values with upper case values
    By Gssoc777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-20-2016, 12:52 PM
  3. Replies: 1
    Last Post: 07-14-2016, 05:51 AM
  4. Search Upper Case
    By VegasL in forum Excel General
    Replies: 8
    Last Post: 10-01-2008, 02:44 AM
  5. Find Upper Case Text
    By John1950 in forum Excel General
    Replies: 2
    Last Post: 11-25-2005, 10:00 AM
  6. Replies: 14
    Last Post: 08-25-2005, 10:05 PM
  7. Replies: 1
    Last Post: 03-09-2005, 05:06 PM

Tags for this Thread

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