Assign a new number (2003)

Topic

New Reply

I am attaching a worksheet I inherited that contains a list of employee names (fake). I deleted about 2700 rows of data to make the worksheet small enough to share. Anyway, HR says the macros in the worksheet don’t work, and they want me to fix them. First, I really don’t know how to read code well enough to fix it. But, also, when I looked at what they are trying to do, it just seems very cumberson.

They want to generate a new 4 digit employee number for every new employee when they are hired. The problem is that they used to assign these numbers manually, so there are holes in the number sequence. They want the macro to look through the list of existing numbers and generate a new number that isn’t already being used. Then, they want the list to sort alphabetically by the employee’s last name after the number is assigned (I don’t know how critical that step is, but they asked if I can do it – hence I am asking you).

Would you mind taking a look at what the spreadsheet I attached. I left their instructions on it, just as I received it, so you will know what they think they are working with now.

Thank you!!

Reply | Quote

Replies

You could use these two macros:

Sub AddNew()
‘ Keyboard Shortcut: Ctrl+a
Range(“G3”) = Application.WorksheetFunction.Max(Range(“A:A”)) + 1
Range(“A2:B2”).Insert Shift:=xlDown
Range(“A2”) = Range(“G3”)
Range(“B2”) = Range(“G2”)
Range(“A1”).CurrentRegion.Sort Key1:=Range(“B1”), Header:=xlYes
End Sub

Sub PrintNew()
‘ Keyboard Shortcut: Ctrl+b
Sheets(“Regular Time Sheet”).PrintOut
Sheets(“Overtime Sheet”).PrintOut
End Sub

See attached version.

Reply | Quote

That works great, Hans. I showed them, and they liked it. But, they have asked if we can use some of the missing numbers, and not just have the new number be in consecutive order.

Reply | Quote

That would make the code *much* more complicated, sorry.

Reply | Quote

Add this function to the module:

Function NextNumber()
  Dim x As Long
  Dim y As Long
  With Application.WorksheetFunction
    For x = 1 To .Max(Range("A:A")) + 1
      y = 0
      On Error Resume Next
      y = .Match(x, Range("A:A"), 0)
      On Error GoTo 0
      If y = 0 Then
        NextNumber = x
        Exit Function
      End If
    Next
  End With
End Function

And change Hans’ first procedure to:

Sub AddNew()
' Keyboard Shortcut: Ctrl+a
  Range("G3") = NextNumber
  Range("A2:B2").Insert Shift:=xlDown
  Range("A2") = Range("G3")
  Range("B2") = Range("G2")
  Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlYes
End Sub

The function starts with 1 and increments until it finds the first available number. If you want to start with something other than 1 change the 1 in the line to whatever number you want to start with.

    For x = 1 To .Max(Range("A:A")) + 1

Steve

Reply | Quote

Uh.. how do I add a function? I’m sorry, I don’t know enough about code to do any more than copy/paste it. When you say add a function to the module, please tell me how to do that? I know how to get to the code Hans gave me. But, where do I copy/paste yours to add it? Thank you so much for trying to help me.

Reply | Quote

Place the code (copy and paste) I added after the 2 segments of code that Hans has in the workbook…

Steve

Reply | Quote

Yea!! Thank you all. It works perfectly!! I used the code suggestion for the random number also. Thank you all so much!!

Reply | Quote

If you want the number to be random you could use the function:

Function RandNumber()
  Dim x As Long
  Dim y As Long
  y = 0
  With Application.WorksheetFunction
    Do
      x = Int(Rnd * 9999)+1
      On Error Resume Next
      y = .Match(x, Range("A:A"), 0)
      On Error GoTo 0
    Loop Until y = 0
    RandNumber = x
  End With
End Function

And change the line in “AddNew” to:
Range(“G3”) = RandNumber

Steve

Reply | Quote