Thread: Assign a new number (2003)

1. Assign a new number (2003)

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!!

2. Re: Assign a new number (2003)

You could use these two macros:

' 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")
End Sub

Sub PrintNew()
' Keyboard Shortcut: Ctrl+b
Sheets("Regular Time Sheet").PrintOut
Sheets("Overtime Sheet").PrintOut
End Sub

See attached version.

3. Re: Assign a new number (2003)

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.

4. Re: Assign a new number (2003)

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

5. Re: Assign a new number (2003)

Add this function to the module:

<pre>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</pre>

And change Hans' first procedure to:

' Keyboard Shortcut: Ctrl+a
Range("G3") = <font color=red>NextNumber</font color=red>
Range("A2:B2").Insert Shift:=xlDown
Range("A2") = Range("G3")
Range("B2") = Range("G2")
End Sub</pre>

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 <font color=red>1</font color=red> in the line to whatever number you want to start with.

<pre> For x = <font color=red>1</font color=red> To .Max(Range("A:A")) + 1</pre>

Steve

6. Re: Assign a new number (2003)

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

<pre>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</pre>

And change the line in "AddNew" to:
Range("G3") = <font color=red>RandNumber</font color=red>

Steve

7. Re: Assign a new number (2003)

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.

8. Re: Assign a new number (2003)

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

Steve

9. Re: Assign a new number (2003)

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•