Auto Serial Number Using VBA

Hi all,

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

Please Login or Register  to view this content.

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

I hope that my quesion is clear,

Thank you,

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





"LoveCandle" <LoveCandle.1yy4hb_1132743009.6461@excelforum-nospam.com> wrote
in message news:LoveCandle.1yy4hb_1132743009.6461@excelforum-nospam.com...
>
> Hi all,
>
> I use the following formula to get an Auto Serial Number for all cells
> in column A.
>
>
> Code:
> --------------------
> =IF(B2<>"",ROW()-1,"")
> --------------------
>
>
> My question is >> how can I get an Auto Serial Number for all cells in
> column A by using VBA.
>
> I hope that my quesion is clear,
>
> Thank you,
>
>
> --
> LoveCandle
> ------------------------------------------------------------------------
> LoveCandle's Profile:
> http://www.excelforum.com/member.php...o&userid=28612
> View this thread: http://www.excelforum.com/showthread...hreadid=487565
>

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)


"LoveCandle" <LoveCandle.1yy4hb_1132743009.6461@excelforum-nospam.com> wrote
in message news:LoveCandle.1yy4hb_1132743009.6461@excelforum-nospam.com...
>
> Hi all,
>
> I use the following formula to get an Auto Serial Number for all cells
> in column A.
>
>
> Code:
> --------------------
> =IF(B2<>"",ROW()-1,"")
> --------------------
>
>
> My question is >> how can I get an Auto Serial Number for all cells in
> column A by using VBA.
>
> I hope that my quesion is clear,
>
> Thank you,
>
>
> --
> LoveCandle
> ------------------------------------------------------------------------
> LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
> View this thread: http://www.excelforum.com/showthread...hreadid=487565
>

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
Dear Sir how can I use this formula for Auto Serial number for specified range for exp A3 to A33 please help
Thanks