View Full Version : Autonumber

DarReNz

10-19-2005, 05:52 PM

Hi i have a column named Number and i wish to input autonumbers which will automatically add the number in each row starting from row A3 ? I want the autonumber to work when each row of value is input from another worksheet. how do i do this ? Thanks

Number

A3 1

A4 2

A5 3

Cyberdude

10-19-2005, 08:58 PM

If you haven't done so already, call up the Excel Help facility, and do a search on "Autonumber". There may be some things in there that can get you started, especially about midway down the page where they talk about lists.

malik641

10-19-2005, 09:14 PM

Hey DarReNz, welcome to the forums :hi:

Here's something simple to start you out...just something quick I typed up.

It works with copied and pasted cells, but it's a little slow because it's in a worksheet_change event, and everytime a number is placed under the "Number" column, the worksheet changes...repeating the macro too many times (at least this is my assumption).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim tRows As Long

Dim i As Long

If Target(1, 1).Row < 3 Or Target(1, 1).Value = vbNullString Then Exit Sub

tRows = Target.Rows.Count

For i = 1 To tRows

cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - 2

Next i

End Sub Still workin' on it...:think:

EDIT: Oh yeah, BTW, it only works with the fact that your "Number" column starts in A3...I'll work on that too.

lucas

10-19-2005, 09:24 PM

Very nice solution Joseph

malik641

10-19-2005, 09:36 PM

K, got it :thumb

Check it out:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

Dim NumRow As Long

Dim tRows As Long

Dim i As Long

Dim cell As Variant

For Each cell In cells(1, 1).End(xlDown)

If cell.Value = "Number" Then

NumRow = cell.Row

Exit For

End If

Next cell

If Target(1, 1).Row <= NumRow Or Target(1, 1).Value = vbNullString Then

Application.EnableEvents = True

Exit Sub

End If

tRows = Target.Rows.Count

For i = 1 To tRows

cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - NumRow

Next i

Application.EnableEvents = True

End Sub Now this will work no matter where you have the "Number" field as long as it's in column A. So you could have this in "A6" or "A600" and it still counts correctly.

It won't delete the number though if you delete the values to the right of the number...unless you use this method:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

Dim NumRow As Long

Dim tRows As Long

Dim i As Long

Dim cell As Variant

For Each cell In cells(1, 1).End(xlDown)

If cell.Value = "Number" Then

NumRow = cell.Row

Exit For

End If

Next cell

If Target(1, 1).Row <= NumRow Then

Application.EnableEvents = True

Exit Sub

End If

tRows = Target.Rows.Count

If Target(1, 1).Value = vbNullString Then

For i = 1 To tRows

cells(Target(i, 1).Row, 1).Clear

Next i

Application.EnableEvents = True

Exit Sub

End If

For i = 1 To tRows

cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - NumRow

Next i

Application.EnableEvents = True

End Sub Hope this helps :thumb

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.