+ Reply to Thread
Results 1 to 10 of 10

Using the Find method in a range from bottom to top of range

  1. #1
    Forum Contributor
    Join Date
    12-15-2009
    Location
    Herndon, VA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Using the Find method in a range from bottom to top of range

    I have a range. Inside of that range I have some blank cells. I want to use the find method to find and locate those blank cells, but I want to start from the bottom of the range, and work my way up. So starting from the bottom of the range (E29), locate the first blank cell above E29.

    The arguments of the Find method do not seem to have this option. Is this even possible?


    Please Login or Register  to view this content.
    Last edited by Excel_vba; 12-20-2013 at 06:11 PM. Reason: Putting tags around my code.

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Using the Find method in a range from bottom to top of range

    If you have other data in the sheet, here's an alternative way:

    Please Login or Register  to view this content.

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

    Re: Using the Find method in a range from bottom to top of range

    OP complied with request for code tags
    Last edited by 6StringJazzer; 12-23-2013 at 09:07 AM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Using the Find method in a range from bottom to top of range

    Using the find function, try this:

    Please Login or Register  to view this content.
    Last edited by berlan; 12-21-2013 at 12:12 AM.

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Using the Find method in a range from bottom to top of range

    method to find and locate those blank cells
    or

    So starting from the bottom of the range (E29), locate the first blank cell above E29
    so locate all blank cells or the first above E29?

    If the last case perhaps something like this.

    Please Login or Register  to view this content.
    Alf

  6. #6
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Using the Find method in a range from bottom to top of range

    @Excel_vba, did it work out for you?

  7. #7
    Forum Contributor
    Join Date
    12-15-2009
    Location
    Herndon, VA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Using the Find method in a range from bottom to top of range

    Quote Originally Posted by berlan View Post
    @Excel_vba, did it work out for you?
    I forgot to mention I want to loop from the bottom. Here is what I have:

    Please Login or Register  to view this content.
    I am getting an error on the Range line saying "Object Variable or With Block variable not set"

    Must be something wrong with the way I am specifying a Range?

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

    Re: Using the Find method in a range from bottom to top of range

    Can you upload a small sample workbook that clearly shows what you are trying to do?

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

    Re: Using the Find method in a range from bottom to top of range

    Quote Originally Posted by Excel_vba View Post
    I forgot to mention I want to loop from the bottom. Here is what I have:
    You are trying to use two different methods at the same time. I will separate the two and show how to use each one. But first let's clarify the problem you are a trying to solve.

    Your first post said "locate the first blank cell above E29" but your code shows a loop that finds every blank cell in column E from row 9 to 29. Because you are checking every cell in that range, why does it matter whether you go from bottom to top, or top to bottom? Normally direction matters if you are inserting or deleting rows but you don't seem to be doing that.

    Here are two methods, assuming you want to check every cell in E9:E29, from the bottom up, and fill in blank cells with a string.

    One method is to loop through every row. If you do that there is no reason to use Find.

    Please Login or Register  to view this content.
    The other method is to let Find check the cells for you, although a different type of loop is necessary. This is a little more complicated and probably overkill for your situation.

    Edit: I modified this code to remove the variable FirstFound because it's not necessary if you are changing every cell after you find it. Normally you need FirstFound because Find search wraps around, but if you change every found cell then eventually Find won't find anything.

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 12-23-2013 at 09:40 AM.

  10. #10
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Using the Find method in a range from bottom to top of range

    If your (initial) purpose was to only fill the last blank cell, this is an alternative:

    Please Login or Register  to view this content.
    Last edited by berlan; 12-23-2013 at 11:09 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. Find Method returns a string instead of a Range
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2009, 04:49 PM
  2. add range in find method
    By ilkamalo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2009, 03:44 AM
  3. Replies: 2
    Last Post: 01-26-2009, 07:59 PM
  4. Find top/bottom 3 values from a range
    By Andrew-Mark in forum Excel General
    Replies: 10
    Last Post: 08-14-2008, 10:08 AM
  5. [SOLVED] Find method fails in hidden range
    By Rick Hansen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2006, 06:00 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