numbering macro not working....

[cbrf23]

Hi, I wrote a macro that is suppposed to add a number to column a when I enter data in column b. I dont want this to be the row number, i just want sequential numbering of these items. I'm not getting any errors....but I'm also not getting any numbers. Can anyone tell me what I'm doing wrong?

Private Sub AUTO_NUMBER(ByVal Target As Range)
Dim ItemNum
On Error GoTo ERRHANDLER
ItemNum = Value.Target.Offset(-1, -1) + 1

 
    If (Not Intersect(Target, Range("B:B")) Is Nothing) Then
            If (Not IsEmpty(Target)) Then
                    Target.Offset(0, -1) = ItemNum
                Else
                    With Target.Offset(0, -1)
                    .ClearContents
                    End With
            End If
    End If
 
Exit Sub
ERRHANDLER:
MsgBox "SOMETHINGS NOT RIGHT..."
Exit Sub
 
End Sub

 

[VoG]

If you want it to run automatically it would need to be

Private Sub Worksheet_Change(ByVal Target As Range)

 

[doofusboy]

Try this instead [it seems to work]:

Private Sub AUTO_NUMBER(ByVal Target As Range)
Dim ItemNum
On Error GoTo ERRHANDLER
    If (Not Intersect(Target, Range("B:B")) Is Nothing) Then
            If (Not IsEmpty(Target)) Then
                    [COLOR=red][B]ItemNum = Target.Offset(-1, -1).Value + 1[/B][/COLOR]
                    Target.Offset(0, -1) = ItemNum
                Else
                    With Target.Offset(0, -1)
                    .ClearContents
                    End With
            End If
    End If
Exit Sub
ERRHANDLER:
MsgBox "SOMETHINGS NOT RIGHT..."
Exit Sub
 
End Sub

Your code was erroring on:

ItemNum = Value.Target.Offset(-1, -1) + 1

 

[cbrf23]

thank you VoG.

doofusboy, thank you very much.

Everything is working good now!

 

[cbrf23]

So, I've been playing a little with my error handler and I'm trying to get it to automatically update my numbers on a row/multiple row delete?

I'd like it if I delete a row, that it would re-number from that point down so the numbers dont get out of order...

So...if I have entries from B3 to B23 and I delete row 10, then rows 11-23 should get re-numbered in sequence.

Does that make sense?

I tried adding into the error handler something like:

If Err.Number = 1004 Then  '<---1004 is error code returned when I deleted rows.
           If (Not IsEmpty(Target)) Then
                    ItemNum = Target.Offset(-1, -1).Value + 1
                    Target.Offset(0, -1) = ItemNum
                Else
                    With Target.Offset(0, -1)
                    .ClearContents
                    End With
            End If
     Do Until Target = ""
          Target.offset(-1,0).Select
          Loop
Exit Sub

But that did not seem to work....