Selecting next blank cell

[Yellowdude]

Ive got a table and i want excel to automatically go through all the table untill it finds a blank cell and then add an x to the empty cell. Is there a formula that can do this or a macro?

 

[Guest]

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.

 

[Tom Urtis]

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

T.U.

 

[Legacy 344]

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

 

[Yellowdude]

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?

 

[Legacy 344]

Why don't you try them to find out?

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

 

[Yellowdude]

Thnks a lot everyone

 

[Mark W.]

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

 

[Legacy 344]

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

 

[Mark W.]

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

 

[Legacy 344]

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.