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?

 

[Yellowdude]

Thats great but is it possible to only put the x in the next blank cell not them all
This message was edited by Yellowdude on 2002-03-05 12:42

 

[Mark W.]

On 2002-03-05 12:42, Yellowdude wrote:
Thats great but is it possible to only put the x in the next blank cell not them all
This message was edited by Yellowdude on 2002-03-05 12:42

Define "next". Describe your table.

 

[Yellowdude]

ive got a table 10 x 10 which is blank .
I want to create a marco which will got through the table and place an x in the next empty cell. It would go through row A then B etc untill if finds and empty cell and fills it in
This message was edited by Yellowdude on 2002-03-05 13:02

 

[Mark W.]

Row "A" or column "A"?

 

[Guest]

On 2002-03-05 13:26, Mark W. wrote:
Row "A" or column "A"?

soor i meant column A

 

[Mark W.]

Okay, here's where you need a VBA guru...

Just loop through each sucessive column and perform...

Selection.SpecialCells(xlCellTypeBlanks).Select

until you find a blank cell and then enter "x" into the active cell.

 

[Tom Urtis]

Try this macro, it starts in column A and places an "X" in the next available row of columns A:J, up to and icluding row 11. I coded for row 11 because you said the table is 10 X 10, and I thought that may mean row 1 is a header and rows 2:11 are the 10 rows of data. If I guessed incorrectly, please modify accordingly.

Sub X_MarksTheSpot()
Application.ScreenUpdating = False
[A1].Activate
Do Until ActiveCell.Column = 11
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(1, 0) = "X"
ElseIf ActiveCell.End(xlDown).Offset(1, 0).Row > 11 Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.End(xlDown).Offset(1, 0) = "X"
ActiveCell.Offset(0, 1).Activate
End If
Loop
[A1].Select
Application.ScreenUpdating = True
End Sub

Any help?

Tom Urtis

 

[Mark W.]

Tom, can't this be done with fewer conditions and using Selection.SpecialCells(xlCellTypeBlanks).Select?
This message was edited by Mark W. on 2002-03-05 13:38

 

[Tom Urtis]

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

 

[Mark W.]

Right, but why not use "Selection.SpecialCells(xlCellTypeBlanks).Select" to find the empty cell instead of testing cell by cell. Once you've selected the cell(s) then you restrict the selection to the active cell produced by "Selection.SpecialCells(xlCellTypeBlanks).Select".

Wouldn't this algorithm run faster, or am I mistaken to believe that using Excel's native commands are optimized and; therefore, preferable worksheet navigation.
This message was edited by Mark W. on 2002-03-05 15:02