Consulting

Results 1 to 7 of 7

Thread: Adding batches of serial numbers

  1. #1
    VBAX Newbie
    Joined
    May 2018
    Posts
    4
    Location

    Adding batches of serial numbers

    Hi,

    I have to keep track of the location of batches of certificates. To do this I want a spreadsheet which will show the 3 letter prefix in column A, and the first certificate number of each pack within the batch in column B.
    Cell H1 contains the number of certificates in each pack
    So far I have:

    Sub AddNewPacks()
    
    Dim Amount As String, Prefix As String, Firstno As String, Counter As Integer, Thisgo As Integer
    Prefix = InputBox("Please enter the Prefix of the certificates being added")
    Amount = InputBox("Please enter the number of packs to be added")
    If Amount = "" Then Exit Sub
    If Amount > 1000 Then
    MsgBox "Invalid Amount. Number of packs cannot be more than 1000"
    Exit Sub
    End If
    For Thisgo = 1 To Amount
    Range("A1").End(xlDown).Offset(1, 0).Value = Prefix
    Next Thisgo
    
    Firstno = InputBox("Please enter the low st serial number")
    If Firstno > 999999 Then
    MsgBox "Invalid Serial Number."
    Exit Sub
    End If
    Range("B1").End(xlDown).Offset(1, 0).Value = Firstno
    For Counter = 1 To Amount
    ActiveCell.Offset(1, 0).Value = ActiveCell + H1
    Next Counter
    End Sub
    This puts the prefix in A the correct number of times, but I can't get the serial number part to work.
    Any help would be appreciated
    Last edited by Paul_Hossler; 05-21-2018 at 08:15 AM. Reason: Added CODE tags - you can use the # icon

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. I added CODE tags around your macro to set it off and do a little formatting -- you can use the[#] icon to insert beginning and ending CODE tags to paste the macro between

    2. "Can't get it to work" is a little (OK, a lot) ambiguous. An error message or wrong result usually helps

    3. Guessing just by looking at the code, you probably wanted this

            ActiveCell.Offset(1, 0).Value = ActiveCell + Range("H1")
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Test:
    Sub AddNewPacks2()
    Dim Amount As String, Prefix As String, Firstno As String
    Prefix = InputBox("Please enter the Prefix of the certificates being added")
    Amount = InputBox("Please enter the number of packs to be added")
    If Amount = "" Then Exit Sub
    If Amount > 1000 Then
      MsgBox "Invalid Amount. Number of packs cannot be more than 1000"
      Exit Sub
    End If
    Firstno = InputBox("Please enter the low st serial number")
    If Firstno > 999999 Then
      MsgBox "Invalid Serial Number."
      Exit Sub
    End If
    With Range("A1").End(xlDown).Offset(1).Resize(Amount)
      .Value = Prefix
      .Offset(, 1).FormulaR1C1 = "=R[-1]C+1"
      .Offset(, 1).Cells(1).Value = Firstno
      .Offset(, 1).Value = .Offset(, 1).Value
    End With
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Newbie
    Joined
    May 2018
    Posts
    4
    Location
    Paul - Thanks for setting me straight
    Last edited by Mikebe; 05-22-2018 at 12:21 AM. Reason: error

  5. #5
    VBAX Newbie
    Joined
    May 2018
    Posts
    4
    Location
    P45cal - Thank you, that works exactly as I hoped except I need the values in column B to increase by the value in H1

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    change:
    .Offset(, 1).FormulaR1C1 = "=R[-1]C+1"
    to:
    .Offset(, 1).FormulaR1C1 = "=R[-1]C+R1C8"
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Newbie
    Joined
    May 2018
    Posts
    4
    Location
    Amazing

    Thank you

Tags for this Thread

Posting Permissions

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