+ Reply to Thread
Results 1 to 5 of 5

VBA code for searching text in a cell

  1. #1
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Question VBA code for searching text in a cell

    Hi all,

    I have 2 very closely related problems that probably require very similar code:

    1). In a sheet called "4. Class Data", the content of cell A1 is: "A123 Example Rd, Town Site 1 ATC Report"
    In VBA, I have a String variable called "Site_Ref"
    I need a line of code which extracts the "Site 1" bit from the cell reference and assigns it to Site_Ref.
    This code will be applied to different cells which will have the same wording format as above, but different Site #.
    I know it should look something like this, but this would extract the whole cell content, not just the middle bit I need:
    Please Login or Register  to view this content.

    2). Cell A2 in my active sheet has the value "Class Data, Day 1 - Monday 6th, June 2022"
    I have an If statement in VBA that says if cell A2 contains the word "day", Then continue through the if statement.
    The following doesn't work:
    Please Login or Register  to view this content.
    Any help on either issue (or both) would be greatly appreciated!!
    Last edited by 6StringJazzer; 06-23-2022 at 02:46 PM. Reason: fixed errors in code tags

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA code for searching text in a cell

    1. If the site number is 1 digit,

    Please Login or Register  to view this content.
    If it can be any number of digits it's going to be more complicated.


    2.
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Re: VBA code for searching text in a cell

    Quote Originally Posted by 6StringJazzer View Post
    1. If the site number is 1 digit,

    Please Login or Register  to view this content.
    If it can be any number of digits it's going to be more complicated.


    2.
    Please Login or Register  to view this content.
    Thanks very much for your code for problem one. Works a dream.
    Line 2 doesn't seem to work though.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA code for searching text in a cell

    Sorry, extra paren causes compile error. use this

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Re: VBA code for searching text in a cell

    Quote Originally Posted by 6StringJazzer View Post
    Sorry, extra paren causes compile error. use this

    Please Login or Register  to view this content.
    Doesn't work either I'm afraid. But I've now found an alternative approach. Thanks for your help
    Last edited by aurelien_21; 06-24-2022 at 09:57 AM.

+ 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. Searching cell for text
    By Jackx1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-16-2020, 11:53 PM
  2. help with code for searching for cell value
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-18-2014, 04:02 PM
  3. [SOLVED] Searching a Range for a Specific Text and put a different text in a different cell
    By AngelAzrael in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-14-2013, 09:25 AM
  4. Replies: 19
    Last Post: 04-10-2013, 08:30 AM
  5. [SOLVED] Searching cell for text in specific location and pasting result in another cell
    By AWM1966 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-14-2012, 12:02 AM
  6. Adjust code for searching up to clear cell
    By pktrusty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2010, 03:21 AM
  7. searching for text within a cell
    By okanem in forum Excel General
    Replies: 3
    Last Post: 03-22-2005, 05:49 AM

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