PDA

View Full Version : [SOLVED] Fill ascending numbers



Dreamer
06-27-2005, 07:57 AM
Hi,

col A Col B
A


B


C

--> Anyone can suggest how to fill the numbes in col A like this...

Col A Col B
1 A
1
1
2 B
2
3 C
3


~ Thank you ~ :help

austenr
06-27-2005, 08:22 AM
How many rows do you want to fill in each column or do you want to fill all of the rows?

Bob Phillips
06-27-2005, 08:55 AM
col A Col B
A
B
C
--> Anyone can suggest how to fill the numbes in col A like this...

Col A Col B
1 A
1
1
2 B
2
3 C
3



Dim iLastRow As Long
Dim i As Long
Dim idx As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value <> "" Then
idx = idx + 1
Cells(i, "B").Value = Cells(i, "A").Value
End If
Cells(i, "A").Value = idx
Next i

mdmackillop
06-27-2005, 09:32 AM
Hi Dreamer,
It's not totally clear from the layout if the letters are to be shifted to Coumn B or not. If everything is to be kept in column A, this variation to XLD's code will do that.



Dim iLastRow As Long
Dim i As Long
Dim idx As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value <> "" Then
idx = idx + 1
Cells(i, "A").Value = idx & Cells(i, "A").Value
Else
Cells(i, "A").Value = idx
End If
Next i

Dreamer
06-27-2005, 10:07 AM
Hello, just one column to be filled, but a total of 30000 rows....

Let me explain it more clearly, sorri for unclear information.. :bow:
My desired format is..

col A Col B
1 -----A
1
1
2 -----B
2

Originally, one column exists with Letters A,B.
Ideally, I want to insert a new row (A) with outputs as shown above.

mdmackillop
06-27-2005, 02:31 PM
To insert an additional column, try:


Dim iLastRow As Long
Dim i As Long
Dim idx As Long
Columns("A:A").Insert Shift:=xlToRight
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "B").Value <> "" Then idx = idx + 1
Cells(i, "A").Value = idx
Next i