Help with some sheet code
 

I have the following code which adds an ID number to rows of data entered
onto a certain sheet. The number is entered into column A when an entry is
made into column B.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Count = 1 Then
If IsEmpty(Cells(Target.Row, 1)) Then
Application.EnableEvents = False
Target.Offset(0, -1).Value = Target.Row - 1
End If
End If
Application.EnableEvents = True
End Sub

This works fine but I have found myself in the situation where 100's of rows
of data are copied to the sheet, an ID number is not created when I do this.
Is it possible to amend the existing code to accomodate both methods of data
entry onto the sheet?

Thanks in advance.

Gareth

PS
Is there a need for a error handler?

Help with some sheet code
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, rng1 as Range
If Target.Column = 2 And Target.Count = 1 Then
Application.EnableEvents = False
set rng = Range(cells(2,1),Cells(rows.count,1).End(xlup))
if Intersect(rng, cells(1,1)) is Nothing then
set rng1 = rng.specialcells(xlblanks)
rng1.Formula = "=row()-1"
rng.formula = rng.value
else
Target.Value = Target.row-1
end if
End If
Application.EnableEvents = True
End Sub

Regards,
Tom Ogilvy


Gareth wrote in message
...
I have the following code which adds an ID number to rows of data entered
onto a certain sheet. The number is entered into column A when an entry
is
made into column B.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Count = 1 Then
If IsEmpty(Cells(Target.Row, 1)) Then
Application.EnableEvents = False
Target.Offset(0, -1).Value = Target.Row - 1
End If
End If
Application.EnableEvents = True
End Sub

This works fine but I have found myself in the situation where 100's of
rows
of data are copied to the sheet, an ID number is not created when I do
this.
Is it possible to amend the existing code to accomodate both methods of
data
entry onto the sheet?

Thanks in advance.

Gareth

PS
Is there a need for a error handler?