+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 20

next empty cell in column, from cell that contains "xxx"

  1. #1
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    next empty cell in column, from cell that contains "xxx"

    Hello.
    I have this useful piece of code that enters data to the next empty cell in a specified column.
    Please Login or Register  to view this content.
    I wonder if there is a way to manipulate it so that it finds the cell in column "A" that contains the text "xxx", and then finds the next empty cell down from there...

    Any ideas? Cheers.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,016

    Re: next empty cell in column, from cell that contains "xxx"

    Try (Change references where required.)
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    Re: next empty cell in column, from cell that contains "xxx"

    I've tested this along with adjustments and it doesn't seem to work. I believe there are 2 issues..

    1) If there is text somewhere below "xxx", on the way up looking for the next empty cell it will stop there, as oppose to going past it and to the next empty cell below "xxx"

    2) As it goes through each cell in MyRange, it enters them all in the same cell on the active worksheet. As oppose to listing them. So I end up with with last value in one cell and that's it.

    Thank you for the suggestion though, I will keep investigating and looking for a way to make it work in the mean time.

  4. #4
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    908

    Re: next empty cell in column, from cell that contains "xxx"

    Quote Originally Posted by mruu View Post
    I've tested this along with adjustments and it doesn't seem to work. I believe there are 2 issues...
    Did you actually try the code as it was posted? I ask because you are talking about "looking up" when jolivanes' code is "looking down".

  5. #5
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    Re: next empty cell in column, from cell that contains "xxx"

    I did... I say that because the data wasn't being entered in the next blank cell after "xxx". It was going into just the next blank cell from the bottom. So seemed like it was going UP.
    I thought that was odd as well but that is what was happening.

    I've started writing this code instead using .Find, I'm just trying to figure out how to replace "Range" with "value" or something..

    Please Login or Register  to view this content.

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,155

    Re: next empty cell in column, from cell that contains "xxx"

    Perhaps

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  7. #7
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    908

    Re: next empty cell in column, from cell that contains "xxx"

    Quote Originally Posted by mruu View Post
    I say that because the data wasn't being entered in the next blank cell after "xxx". It was going into just the next blank cell from the bottom.
    That means the "blank" cells you think you have are not actually blank... there is something in them even if it is just the empty text string ("").

  8. #8
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    Re: next empty cell in column, from cell that contains "xxx"

    Sorry, no, I just realised how it's working.
    There is an empty cell immediately after "xxx" so (xlDown) takes it to the next occupied cell. Then offsets from there.
    I need to get to the next empty cell after "xxx"

  9. #9
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    Re: next empty cell in column, from cell that contains "xxx"

    Ok this is my solution that seems to do the job:

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,016

    Re: next empty cell in column, from cell that contains "xxx"

    No selecting/activating needed.
    Please Login or Register  to view this content.
    As long as the "xxx" is not in the first row.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    15,493

    Re: next empty cell in column, from cell that contains "xxx"

    Perhaps
    Please Login or Register  to view this content.

  12. #12
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    908

    Re: next empty cell in column, from cell that contains "xxx"

    As long as at least one cell in Column A contains "xxx", this code will work (change the 12345 to whatever it is you want to assign to that first blank cell)...
    Please Login or Register  to view this content.
    If you never do searches using format properties, then you can shorten this macro to this...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 10-14-2019 at 02:47 AM.

  13. #13
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    Re: next empty cell in column, from cell that contains "xxx"

    Thanks everyone for your help. As mentioned, I came up with a solution that works. For what I'm doing I do actually need to activate a cell.

    Jindon & Rick, your lines do work perfectly as well.

    Jolivanes there's still an issue with yours where it keeps going to the same cell below "xxx".

    Thanks again all, really appreciate it.

  14. #14
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    908

    Re: next empty cell in column, from cell that contains "xxx"

    Quote Originally Posted by mruu View Post
    For what I'm doing I do actually need to activate a cell.
    My code and jindon's code do not need any cells to be activated to work, but you can always add a line of code to either of them in order to activate a cell for later use in other procedures. For my code...
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    06-23-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    48

    Re: next empty cell in column, from cell that contains "xxx"

    Yes, but I mean further along I need a cell to be activated.. for following procedures.
    And yes that is exactly what I did to your code to make it work for me, thanks Rick!

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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