+ Reply to Thread
Results 1 to 10 of 10

How to search a column vertically for a text string

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question How to search a column vertically for a text string

    Hi All,

    I have a spreadsheet that lists calendar dates, sequentially and vertically, along column C.
    Would anyone know how to locate the row number that today's date has been found in?

    ps: I don't need to use the below code, but I tried to make the below work. Unfortunately no luck:
    Please Login or Register  to view this content.
    Unfortunately in the above example, "foundInRowNumber" doesn't show the row number. Instead it incorrectly has today's date.

    Thanks so much everyone for your help,
    jmc

  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: How to search a column vertically for a text string

    Hi

    If the dates are in ascending sequence then
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    if not then just add FALSE as a third element to the MATCH() function.
    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
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: How to search a column vertically for a text string

    This works on a test file I made.

    Please Login or Register  to view this content.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

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

    Re: How to search a column vertically for a text string

    Try something like
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question Re: How to search a column vertically for a text string

    Hi Skywriter & Richard,

    Skywriter, your code works (almost) perfectly, but I should have mentioned that column C skips over some dates.

    If the date is not found in column C, it gives the error: "Object variable or With block variable not set".

    If not found, would you/someone know how to ignore that error, so that foundInRowNumber remains null?

    //

    I also tried Richard's approach, but that gives a sub or function not defined error. I'll see if I can also find a workaround for that.

    Thanks so much to you both for the help so far!!
    -jmc

  6. #6
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Re: How to search a column vertically for a text string

    Thanks so much everyone!

    I used the below code to get it working, and also ignores pop-up errors, if the date is not found in column C:

    Please Login or Register  to view this content.
    If in the event the date is not in column C, then foundInRowNumber would remain as null.

  7. #7
    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: How to search a column vertically for a text string

    Quote Originally Posted by jmccoughlin View Post

    I also tried Richard's approach, but that gives a sub or function not defined error. I'll see if I can also find a workaround for that.

    Thanks so much to you both for the help so far!!
    -jmc
    My offering wasn't VBA it was a standard Excel function. If you want to use the row value it returns in VBA then just read the cell into a VBA variable. I always think it's better to use standard functions where possible rather than harder to read VBA which after all is only replicating in a much slower way what the machine code function will do more efficiently.

  8. #8
    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: How to search a column vertically for a text string

    Quote Originally Posted by jmccoughlin View Post

    I also tried Richard's approach, but that gives a sub or function not defined error. I'll see if I can also find a workaround for that.

    Thanks so much to you both for the help so far!!
    -jmc
    My offering wasn't VBA it was a standard Excel function. If you want to use the row value it returns in VBA then just read the cell into a VBA variable. I always think it's better to use standard functions where possible rather than harder to read VBA which after all is only replicating in a much slower way what the machine code function will do more efficiently.

  9. #9
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Re: How to search a column vertically for a text string

    Hm, interesting idea. Thanks, Richard! I had no idea that was possible -- I'll give that a shot now! -jmc

  10. #10
    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: How to search a column vertically for a text string

    Quote Originally Posted by jmccoughlin View Post
    Hm, interesting idea. Thanks, Richard! I had no idea that was possible -- I'll give that a shot now! -jmc
    Hi,

    Yes as I intimated I find the technique of using excel functions to control things or supply information much more user friendly than having to worry about vba code which might do the same thing. For instance if you want to check that a particular range of say 5 cells has been filled in then use an

    =IF(COUNTA(A1:A5)=5,0,1)

    and in vba check for the value zero and if necessary issue a warning message

+ 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. [SOLVED] Macro to Search column for Text string and Copy Entire Row to another Worksheet
    By TJacks1908 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2013, 07:11 AM
  2. [SOLVED] VBA to Search a Text String, Find a Value listed in another column and replace the text
    By Jschrod303 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2012, 08:56 PM
  3. Replies: 3
    Last Post: 07-27-2011, 02:29 PM
  4. Replies: 1
    Last Post: 07-26-2011, 06:48 AM
  5. Search a column for a text string
    By shawnh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2011, 02:22 PM
  6. Replies: 3
    Last Post: 07-16-2010, 09:45 AM
  7. Replies: 3
    Last Post: 03-12-2010, 02:57 PM
  8. Search column - return row found in long text string
    By JayL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2005, 12:06 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