Inserting value with increment by 1 from previous value

I need unique ID in Row 1

How do i modify this code to have value of ( 1+value in column A from 1 row
above ) instead of static 1



Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo errHandler:

If Target.Column = 2 And Target.Value <> "" Then
Cells(Target.Row, 3).Value = "New"
Cells(Target.Row, 1).Value = 1
Cells(Target.Row, 5).Select
End If

errHandler:
Application.EnableEvents = True

End Sub

I thought that i could search for max in column A and then add 1
Or remember previous value and insert it in variable somehow

how do i best acomplish this task ?

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 2 Then
If Not IsEmpty(.Value) Then
On Error GoTo errHandler
Application.EnableEvents = False
.Offset(0, 1).Value = "New"
.Offset(0, -1).Value = Application.Max(Range("A:A")) + 1
.Offset(0, 3).Select
End If
End If
End With
errHandler:
Application.EnableEvents = True
End Sub




In article <[email protected]>,
"Mikus" <[email protected]> wrote:

> I need unique ID in Row 1
>
> How do i modify this code to have value of ( 1+value in column A from 1 row
> above ) instead of static 1
>
>
>
> Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count > 1 Then Exit Sub
>
> On Error GoTo errHandler:
>
> If Target.Column = 2 And Target.Value <> "" Then
> Cells(Target.Row, 3).Value = "New"
> Cells(Target.Row, 1).Value = 1
> Cells(Target.Row, 5).Select
> End If
>
> errHandler:
> Application.EnableEvents = True
>
> End Sub
>
> I thought that i could search for max in column A and then add 1
> Or remember previous value and insert it in variable somehow
>
> how do i best acomplish this task ?

I already thought up a way to fo this.... i did following:

Do you think i have some serious flaws here ? Is your way better ?

Sub Worksheet_Change(ByVal Target As Range)

Dim iNextValue As Integer
iNextValue = Cells(Rows.Count, "A").End(xlUp).Row

If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo errHandler:

If Target.Column = 2 And Target.Value <> "" Then
Cells(Target.Row, 3).Value = "New"
Cells(Target.Row, 1).Value = iNextValue
Cells(Target.Row, 5).Select
End If

errHandler:
Application.EnableEvents = True

End Sub

"JE McGimpsey" wrote:

> One way:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target
> If .Count > 1 Then Exit Sub
> If .Column = 2 Then
> If Not IsEmpty(.Value) Then
> On Error GoTo errHandler
> Application.EnableEvents = False
> .Offset(0, 1).Value = "New"
> .Offset(0, -1).Value = Application.Max(Range("A:A")) + 1
> .Offset(0, 3).Select
> End If
> End If
> End With
> errHandler:
> Application.EnableEvents = True
> End Sub
>
>
>
>
> In article <[email protected]>,
> "Mikus" <[email protected]> wrote:
>
> > I need unique ID in Row 1
> >
> > How do i modify this code to have value of ( 1+value in column A from 1 row
> > above ) instead of static 1
> >
> >
> >
> > Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Cells.Count > 1 Then Exit Sub
> >
> > On Error GoTo errHandler:
> >
> > If Target.Column = 2 And Target.Value <> "" Then
> > Cells(Target.Row, 3).Value = "New"
> > Cells(Target.Row, 1).Value = 1
> > Cells(Target.Row, 5).Select
> > End If
> >
> > errHandler:
> > Application.EnableEvents = True
> >
> > End Sub
> >
> > I thought that i could search for max in column A and then add 1
> > Or remember previous value and insert it in variable somehow
> >
> > how do i best acomplish this task ?
>

As long as you have no blanks in your list, it's more a matter of style
- I prefer to minimize the number of references by using With..End With,
since it's significantly faster than accessing the referenced cell. But
you probably won't be able to do much with the microseconds...

If you have blanks, then making an entry that fills in the blank will
generate a duplicate ID.



In article <[email protected]>,
"Mikus" <[email protected]> wrote:

> Do you think i have some serious flaws here ? Is your way better ?