auto number cells if not blank
Posted by Andy on November 19, 2001 8:59 AM
How can I add a sequential number, (followed by a period) to a cell, if the cell next to it contains text and then stop as soon as it encounters a blank?
I have to use a macro to perform this task as I have other codes running which need the target cells to be blank until they have finished running!
Been confused with this one for far too long!
Any help would be very gratefully received
Posted by bob Umlas on November 19, 2001 10:26 AM
Assuming the column with the text is B and you want to add these #s to A:
Sub AddNos()
Set nos = Range("B1", Range("B1").End(xlDown)).Offset(0, -1)
nos.Resize(1, 1).Value = 1
nos.Resize(1, 1).AutoFill nos, xlFillSeries
nos.NumberFormat = "General""."""
End Sub
Posted by Dan on November 19, 2001 10:34 AM
This seems kind of LAME, but it seems to work according to what you described. The example puts the sequential numbers in column A and the data you are looking for in column B, starting from row 1. I am also assuming that there will be no blank rows in between rows that have data and that the first row will always have data
In A1 put=
=1&"."
In A2 put=
=IF(B2<>"",1+A1 &".","")
Then copy the formula from A2 down the column. Let me know if that works for you.
