Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign a new number (2003)

    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.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign a new number (2003)

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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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:

    <pre>Sub AddNew()
    ' 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")
    Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlYes
    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •