Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

Thread: Selecting next blank cell

  1. #21
    Guest

    Default

    Hi Mark,

    Using
    Selection.SpecialCells(xlCellTypeBlanks).Select
    hilites all the blank cells in the selection, not just the one in each column that was needing to be specifically populated with an X. I suppose we could have done that by each column, whereby the active cell in that hilited range would have been the cell in question, but we'd still have to loop for the other 9 columns.
    The other thing is, each of the 100 cells in this table is not being evaluated, just the one in row 2, then the next blank one per column, either as being the next between & including 3:11, or 12.
    I'm not sure this answers your question, especially about the algorithms...any advice or ideas are welcome; I see this thread is getting quite a few hits, so maybe someone can help me with answering your question, I just am not sure.

    Tom U.

  2. #22
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmmm, I reached Anonymous status with that last response...lemme look in the mirror and see if I'm still me. Yep.

    T.U.

  3. #23

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think MarkW was suggesting something like this :-

    Dim col%
    On Error Resume Next
    For col = 1 To 11
    Range(Cells(1, col), Cells(10, col)).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Value = "X"
    Next
    On Error GoTo 0

    [ This Message was edited by: Autolycus on 2002-03-05 16:49 ]

  4. #24
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 13:47, Tom Urtis wrote:
    Hey Mark,

    My understanding of the posted string was that he only wanted an "X" in the first blank cell of each column, not all blank cells in each column.

    The code could have been shorter (and still could be modified as such) with fewer conditions if he wants an X in the first available cell from the bottom (row 11) up. But he intimated from the the top down. So, if rows 2:11 were populated with Jack, Bill, Bob, "", "", Tom, Mike, Jim, "", "", my suggestion will place an X in row 5 (the first ""), which is what I think he asked for.

    I'm open for suggestions though, so let me know what you think.

    Tom

    Addendum, what I thought should be taken into account is every possibility: Nothing in row 2, a blank cell between 2:11, or no blank cell. The End, Down, and Offset references, as I understand them, needed to take those possibilities into account.

    T.U.

    [ This Message was edited by: Tom Urtis on 2002-03-05 13:55 ]


    The very above is the scenairo I wished to perform so which of all these method fulfills this?

  5. #25

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why don't you try them to find out?

    (Tom Urtis' code and my code should do the same thing.)

  6. #26
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thnks a lot everyone

  7. #27
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 16:14, Autolycus wrote:
    I think MarkW was suggesting something like this :-

    Dim col%
    On Error Resume Next
    For col = 1 To 11
    Range(Cells(1, col), Cells(10, col)).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Value = "X"
    Next
    On Error GoTo 0

    [ This Message was edited by: Autolycus on 2002-03-05 16:49 ]
    Wow! This is tantalizingly close... and so terse! The only thing that it's missing is some sort of While... loop that stops the "col" loop when it finds the 1st blank. Currently, it's placing an "X" in the 1st blank cell of every column rather than an "X" in the 1st blank encountered in "earliest" column.

    I really like macro code that leverages Excel's built-in functionality!>

    [ This Message was edited by: Mark W. on 2002-03-06 07:30 ]

  8. #28

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 07:25, Mark W. wrote:

    Wow! This is tantalizingly close... and so terse! The only thing that it's missing is some sort of While... loop that stops the "col" loop when it finds the 1st blank. Currently, it's placing an "X" in the 1st blank cell of every column rather than an "X" in the 1st blank encountered in "earliest" column.

    I really like macro code that leverages Excel's built-in functionality!>

    [ This Message was edited by: Mark W. on 2002-03-06 07:30 ]

    I thought that an "X" was required in the first blank cell of every column.
    If it is needed only in the first blank cell of the first column that contains a blank :-

    Dim col%
    For col = 1 To 11
    On Error Resume Next
    Range(Cells(1, col), Cells(10, col)).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Value = "X"
    If Err.Description = "" Then Exit For
    On Error GoTo 0
    Next

  9. #29
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I thought that an "X" was required in the first blank cell of every column.
    If it is needed only in the first blank cell of the first column that contains a blank :-

    Dim col%
    For col = 1 To 11
    On Error Resume Next
    Range(Cells(1, col), Cells(10, col)).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Value = "X"
    If Err.Description = "" Then Exit For
    On Error GoTo 0
    Next
    Sweet! So, I was wondering why "For col = 1 To 11" when the 10x10 matrix only has 10 columns? "For col = 1 To 10" seems to work. Am I missing something?

    >I thought that an "X" was required in the first blank cell of every column.

    BTW, I believe that midway thru this thread the requirement evolved to your original interpetation.

    [ This Message was edited by: Mark W. on 2002-03-06 16:52 ]

  10. #30

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sweet! So, I was wondering why "For col = 1 To 11" when the 10x10 matrix only has 10 columns?

    [ This Message was edited by: Mark W. on 2002-03-06 16:47 ]

    A mistake. Should read 1 to 10.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •