+ Reply to Thread
Results 1 to 4 of 4

Increment Alphanumeric Value of a Cell on each print job

Hybrid View

  1. #1
    Registered User
    Join Date
    08-13-2014
    Location
    Mexico
    MS-Off Ver
    Office 2010
    Posts
    2

    Increment Alphanumeric Value of a Cell on each print job

    hello I need help with the following macro, it works great but only with numeric values but it doesn't work with alphanumeric values.
    Sub PrintJobs()
    Dim i As Long, startnum As Long, lastnum As Long
    
        startnum = Application.InputBox("First Order", "Print Job Number", 1, , , , , 1)
        lastnum = Application.InputBox("Last Order", "Print Job Number", 1, , , , , 1)
    
        For i = startnum To lastnum
            Range("F7").Value = i
            ActiveWindow.SelectedSheets.PrintOut
        Next
    
    End Sub
    for example I want the following value G20141530 in F7 to increment by one after each print so if I print 5 sheets I get G20141530,G20141531,
    G20141532 and so on. any help would be greatly appreciated.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Increment Alphanumeric Value of a Cell on each print job

    Hi Rudy,

    Welcome to the forum!

    This works for the G20141530 - G20141535 :

    Sub RudyPrintJobs()
    Dim i As Long, j As Integer, startnum As String, lastnum As String, Alfa As String
        startnum = Application.InputBox("First Order", "Print Job Number")
        j = 1: Do Until IsNumeric(Mid(startnum, j + 1, 1)): j = j + 1: Loop
        Alfa = Left(startnum, j)
        lastnum = Application.InputBox("Last Order", "Print Job Number")
        If Left(lastnum, j) = Alfa Then
            For i = Right(startnum, Len(startnum) - j) To Right(lastnum, Len(lastnum) - j)
                Range("F7").Value = Alfa & i
                ActiveWindow.SelectedSheets.PrintOut
            Next
        End If
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-13-2014
    Location
    Mexico
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Increment Alphanumeric Value of a Cell on each print job

    thanks xladept for your help, code works great the only thing is when I cancel the printjob I get a debug error. I have underline the debug error in the code.
    
    Sub RudyPrintJobs()
    Dim i As Long, j As Integer, startnum As String, lastnum As String, Alfa As String
        startnum = Application.InputBox("First Order", "Print Job Number")
        j = 1: Do Until IsNumeric(Mid(startnum, j + 1, 1)): j = j + 1: Loop
        Alfa = Left(startnum, j)
        lastnum = Application.InputBox("Last Order", "Print Job Number")
        If Left(lastnum, j) = Alfa Then
            For i = Right(startnum, Len(startnum) - j) To Right(lastnum, Len(lastnum) - j)
                Range("F7").Value = Alfa & i
                ActiveWindow.SelectedSheets.PrintOut
            Next
        End If
    End Sub

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Increment Alphanumeric Value of a Cell on each print job

    Hi Rudy,

    Thanks for the rep!

    Maybe:

    Sub RudyPrintJobs()
    Dim i As Long, j As Integer, startnum As String, lastnum As String, Alfa As String
        startnum = Application.InputBox("First Order", "Print Job Number")
        j = 1: Do Until IsNumeric(Mid(startnum, j + 1, 1)): j = j + 1: Loop
        Alfa = Left(startnum, j)
        lastnum = Application.InputBox("Last Order", "Print Job Number")
        If Left(lastnum, j) = Alfa Then
            For i = Right(startnum, Len(startnum) - j) To Right(lastnum, Len(lastnum) - j)
                Range("F7").Value = Alfa & I
    On Error Resume Next
                ActiveWindow.SelectedSheets.PrintOut
            Next
        End If
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Control Button to increment by 1 and then select next cell in row to increment that cell
    By rammergu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2012, 07:35 PM
  2. [SOLVED] Macro to Increment and Print to PDF filename from merged cell
    By ahjualune in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2012, 06:24 AM
  3. Print Sheet, Increment Cell Formula, Repeat.
    By K_Dub in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2011, 11:46 AM
  4. Replicate and increment alphanumeric keywords?
    By toolsavvy in forum Excel General
    Replies: 7
    Last Post: 12-16-2007, 03:02 AM
  5. How do I auto increment cell value on print in Excel
    By Hiren in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-25-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1