Auto Serial Number Using VBA

[LoveCandle]

Hi all,

I use the following formula to get an Auto Serial Number for all cell
in column A.


Code
-------------------
=IF(B2<>"",ROW()-1,""
-------------------


My question is >> how can I get an Auto Serial Number for all cells i
column A by using VBA.

I hope that my quesion is clear,

Thank you

 

[JMay]

Here's one way: (In a standard module)

Sub Foo()
Range("B2").Select
lrow = Cells(Rows.Count, 2).End(xlUp).Row
Set myrange = Range(Cells(2, 2), Cells(lrow, 2))
For Each cell In myrange
cell.Offset(0, -1).Value = i + 1
i = i + 1
Next cell
End Sub

 

[Bob Phillips]

Is this what you want

For i = 2 To Cells(Rows.Count,"B").End(xlUp).Row
If Cells(i,"B").Value <> "" Then
Cells(i,"A").Value = i - 1
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)