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:
Unfortunately in the above example, "foundInRowNumber" doesn't show the row number. Instead it incorrectly has today's date.
Please Login or Register to view this content.
Thanks so much everyone for your help,
jmc
Hi
If the dates are in ascending sequence then
Formula:
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 iconbelow the post.
This works on a test file I made.
Please Login or Register to view this content.
As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.
Try something like
Please Login or Register to view this content.
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
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:
If in the event the date is not in column C, then foundInRowNumber would remain as null.Please Login or Register to view this content.
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.Originally Posted by jmccoughlin
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks