VBA to create reference number based on another cell
Results 1 to 6 of 6

Thread: VBA to create reference number based on another cell

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to create reference number based on another cell

    Hello All ......

    I need to create a "reference" or "sequence" number like seen in the number column below. This number would only be created when "Weekend Pass" is in the type column and would need to loop through all rows in the worksheet. The quantity and type columns are the only columns with existing data. Any ideas?


    Number Quantity Type
    001 1 Weekend Pass with Night Access
    002 1 Weekend Pass with Night Access
    1 Night Access
    003, 004 2 Weekend Pass
    005 1 Weekend Pass with Night Access
    006, 007 2 Weekend Pass
    1 Monthly
    008 1 Weekend Pass
    009 1 Weekend Pass with Night Access
    3 Monthly
    010, 011, 012 3 Weekend Pass
    1 Monthly
    1 Night Access
    1 Night Access
    013 1 Weekend Pass with Night Access
    014 1 Weekend Pass

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: VBA to create reference number based on another cell

    Try this:-
    Code:
    Sub MG23Jul56
    Dim Rng As Range, Dn As Range, n As Long, c As Long, nstr As String
    Set Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If InStr(Dn.Value, "Weekend Pass") > 0 Then
            For n = 1 To Dn.Offset(, -1)
                c = c + 1
                nstr = nstr & IIf(nstr = "", Format(c, "000"), ", " & Format(c, "000"))
            Next n
        With Dn.Offset(, -2)
            .NumberFormat = "000"
            .Value = nstr: nstr = ""
        End With
    End If
    Next Dn
    End Sub
    Regards Mick

  3. #3
    New Member
    Join Date
    Jan 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to create reference number based on another cell

    Thanks Mick .... awesome, works great, fast response, terrific. But I forgot to mention something . Is it possible to use an OR with InStr? I forgot "Exempt" as a type.

    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    Sub MG23Jul56
    Dim Rng As Range, Dn As Range, n As Long, c As Long, nstr As String
    Set Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If InStr(Dn.Value, "Weekend Pass") > 0 Then
            For n = 1 To Dn.Offset(, -1)
                c = c + 1
                nstr = nstr & IIf(nstr = "", Format(c, "000"), ", " & Format(c, "000"))
            Next n
        With Dn.Offset(, -2)
            .NumberFormat = "000"
            .Value = nstr: nstr = ""
        End With
    End If
    Next Dn
    End Sub
    Regards Mick

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: VBA to create reference number based on another cell

    Try this:-
    Code:
    Sub MG23Jul04
    Dim Rng As Range, Dn As Range, n As Long, c As Long, nstr As String
    Set Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If InStr(Dn.Value, "Weekend Pass") > 0 Or InStr(Dn.Value, "Exempt") > 0 Then
            For n = 1 To Dn.Offset(, -1)
                c = c + 1
                nstr = nstr & IIf(nstr = "", Format(c, "000"), ", " & Format(c, "000"))
            Next n
        With Dn.Offset(, -2)
            .NumberFormat = "000"
            .Value = nstr: nstr = ""
        End With
    End If
    Next Dn
    End Sub
    Regards Mick

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to create reference number based on another cell

    Thank You so much .....

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: VBA to create reference number based on another cell

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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