Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe de celwaarde automatisch te verhogen na elke afdruk?

Stel dat ik een werkbladpagina heb die moet worden afgedrukt 100-kopieën, de cel A1 is het chequenummer Bedrijf-001, nu zou ik willen dat het aantal na elke afdruk met 1 toeneemt. Dat betekent dat wanneer ik het tweede exemplaar afdruk, het nummer automatisch wordt verhoogd naar Company-002, het derde exemplaar, het nummer wordt Company-003 ... honderd exemplaren, het nummer is Company-100. Is er een truc om dit probleem snel en mogelijk in Excel op te lossen?

Auto incrementele celwaarde na elke afdruk met VBA-code


Druk oneven, even, huidige of gespecificeerde pagina's van een werkblad af

Kutools for Excel's Druk gespecificeerde pagina's af hulpprogramma kan u helpen om snel oneven, even, huidige of gespecificeerde pagina's af te drukken als u nodig hebt.

doc increment bij het afdrukken van 2

Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


pijl blauwe rechterbel Auto incrementele celwaarde na elke afdruk met VBA-code


Normaal gesproken is er geen directe manier om deze taak in Excel op te lossen, maar hier maak ik een VBA-code om ermee om te gaan.

1. Houd de toets ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: Celwaarde automatisch verhogen na elke afdruk:

Sub IncrementPrint()
'updateby Extendoffice 20160530
    Dim xCount As Variant
    Dim xScreen As Boolean
    Dim I As Long
    On Error Resume Next
LInput:
    xCount = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
    If TypeName(xCount) = "Boolean" Then Exit Sub
    If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
        MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
        GoTo LInput
    Else
        xScreen = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For I = 1 To xCount
            ActiveSheet.Range("A1").Value = " Company-00" & I
            ActiveSheet.PrintOut
        Next
        ActiveSheet.Range("A1").ClearContents
        Application.ScreenUpdating = xScreen
    End If
End Sub

3. Druk vervolgens op F5 toets om deze code uit te voeren, en er verschijnt een promptvak om u eraan te herinneren het aantal kopieën in te voeren dat u het huidige werkblad wilt afdrukken, zie screenshot:

doc increment bij het afdrukken van 1

4. Klikken OK knop, en uw huidige werkblad is nu aan het afdrukken, en tegelijkertijd zijn de afgedrukte werkbladen genummerd met het bedrijf-001, Bedrijf-002, Bedrijf-003 ... in cel A1 zoals u nodig hebt.

Notes: In de bovenstaande code, de cel A1 worden de sequentienummers ingevoegd die u hebt besteld en de oorspronkelijke celwaarde in A1 wordt gewist. En "Company-00"Is het volgnummer, u kunt ze naar uw behoefte wijzigen.


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen zonder gegevens te verliezen; Inhoud gesplitste cellen; Combineer dubbele rijen / kolommen... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    TBOne · 2 months ago
    I was wondering how to make a small change so that it prints 1 of 10, 2 of 10, 3 of 10, etc.
    Otherwise this works great. Thanks.
  • To post as a guest, your comment is unpublished.
    unknown · 5 months ago
    hey i want to change number of K11 cell number after print to 1-2-3-4-5-6 etc pls can u help ? and also tell me how to call that function pls help
  • To post as a guest, your comment is unpublished.
    Ib Alsa · 6 months ago
    Is there a way to select what values I want to print? for example I printed sequence 1 to 30 but need to reprint sequence 15 to 19 again.
  • To post as a guest, your comment is unpublished.
    Bill Turek · 7 months ago
    Works good for printing incremental #'s. How do I print every 5th,10, when needed?
  • To post as a guest, your comment is unpublished.
    Arash · 9 months ago
    Thanks a million
  • To post as a guest, your comment is unpublished.
    jennifer · 9 months ago
    My cell is I3 and the number is 2298 when I try the (VBA code: Auto increment cell value after each printing:) it gives me 22981 how do I get it to 2298,2299,2300
    • To post as a guest, your comment is unpublished.
      tan chee ho · 8 months ago
      thank you very much, it works for me. And i manage to make a few minor change to suit my needs. Really Appreciate to your sharing.
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hi, jennifer,
      To deal with your problem, please apply the following VBA code:
      Note: Please change the prefix text and number to your own.

      Sub IncrementPrint_Num()
      Dim xCount As Variant
      Dim xScreen As Boolean
      Dim I As Long
      Dim xStr As String
      Dim xInt As Integer
      On Error Resume Next
      xStr = "Company-" 'prefix text
      xInt = 2291 'number
      LInput:
      xCount = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
      If TypeName(xCount) = "Boolean" Then Exit Sub
      If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
      MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
      GoTo LInput
      Else
      xScreen = Application.ScreenUpdating
      Application.ScreenUpdating = False
      For I = 1 To xCount
      xInt = xInt + 1
      ActiveSheet.Range("A1").Value = xStr & xInt
      ActiveSheet.PrintOut
      Next
      ActiveSheet.Range("A1").ClearContents
      Application.ScreenUpdating = xScreen
      End If
      End Sub

      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Richard · 7 months ago
        Hello can you help me with this? I want the xINT to be more than 5 digits. Everytime i put a number with 6 digits, the count goes back to 1. How can i prevent that?
  • To post as a guest, your comment is unpublished.
    Desmond · 10 months ago
    thank you for posting this, it is very helpful. My question is this: I have 2 different barcodes that need to be incremented on one page, how can I modify the code to do that?
  • To post as a guest, your comment is unpublished.
    Pieter · 11 months ago
    thank you for the above, really helpful. is it possible to save and remember the last value
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hello, Pieter,
      To save and remember the last printed value when you print next time, you should apply the following VBA code:

      Sub IncrementPrint()
      Dim xCount As Variant
      Dim xScreen As Boolean
      Dim I As Long
      Dim xM As Long
      Dim xMNWS As Worksheet
      Dim xAWS As Worksheet
      On Error Resume Next
      LInput:
      xCount = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
      If TypeName(xCount) = "Boolean" Then Exit Sub
      If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
      MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
      GoTo LInput
      Else
      xScreen = Application.ScreenUpdating
      Set xAWS = ActiveSheet
      On Error GoTo EMarkNumberSheet
      Set xMNWS = Sheets("IncrementPrint_MarkNumberSheet")
      EMarkNumberSheet:
      If xMNWS Is Nothing Then
      Set xMNWS = Application.Worksheets.Add(Type:=xlWorksheet)
      xMNWS.Name = "IncrementPrint_MarkNumberSheet"
      xMNWS.Range("A1").Value = 0
      xM = 0
      xMNWS.Visible = xlSheetVeryHidden
      Else
      xM = xMNWS.Range("A1").Value
      End If
      Application.ScreenUpdating = False
      For I = 1 To xCount
      xM = xM + 1
      xAWS.Range("A1").Value = " Company-00" & xM
      xAWS.PrintOut
      Next
      xMNWS.Range("A1").Value = xM
      xAWS.Range("A1").ClearContents
      Application.ScreenUpdating = xScreen
      End If
      End Sub

      If you need to reset the printed number to the default number, please run the below code firstly, and then execute the above code to print.

      Sub IncrementPrint_Reinstall()
      Dim xMNWS As Worksheet
      On Error GoTo EMarkNumberSheet
      Set xMNWS = Sheets("IncrementPrint_MarkNumberSheet")
      EMarkNumberSheet:
      If Not xMNWS Is Nothing Then
      Application.DisplayAlerts = False
      xMNWS.Visible = xlSheetHidden
      xMNWS.Delete
      Application.DisplayAlerts = True
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Sniper · 1 years ago
    printed like 30 copies but now i cant print, runned the script a lot of times but not working, dont do anything :(
  • To post as a guest, your comment is unpublished.
    romik · 1 years ago
    my serial number start with 227861 how can i print from
  • To post as a guest, your comment is unpublished.
    Kris · 1 years ago
    Sorry to ask this on a separate post... My serial numbers start with a ZERO, but when I run the program it eliminates the zeros. I tried to convert the number field to text, but that did not fix it. Other ideas?
    • To post as a guest, your comment is unpublished.
      Art · 1 years ago
      R-Click Cell, Format, Custom, Where it says 'General', replace that with as many Zeros as your serial number will be. This will force the amount of zeros needed in front of your serial number. If I have a group of serial numbers that are 10 digit serials, I enter 0000000000 in the Type field to get '0004563571' to display in the serial number field.
      • To post as a guest, your comment is unpublished.
        Kris · 1 years ago
        Thank you Art. I did try that but the barcode kept eliminating the leading zeros... even after doing a custom number format.
  • To post as a guest, your comment is unpublished.
    Kris · 1 years ago
    Thank you for posting this, it is very helpful. My question is this: I have 2 different barcodes that need to be incremented on one page, how can I modify the code to do that?
  • To post as a guest, your comment is unpublished.
    Abdul · 1 years ago
    I need serial numbers like IA1-055242, IA1-055243, IA1-055244 .....
  • To post as a guest, your comment is unpublished.
    Kerry · 2 years ago
    This code is amazing, it is exactly what I need, however, I was wondering if there is a way to start printing from the number that is entered in cell "A1"?
    For example, if I have printed 100 copies, on the next print run I will need to print from number 101 and count up from there.
    I have tried a few code adjustments but it only seems to take the number entered in the cell i.e. 101, add 1 and then the rest of the prints are stuck with that one number, i.e. 102...

    Your assistance would be greatly appreciated :-)
    • To post as a guest, your comment is unpublished.
      Valentas · 1 years ago
      If you did't find a solution already you can edit line 17 of the code to this: ActiveSheet.Range("A1").Value = Range("A1").Value + 1
      This will ad +1 to the number you have in A1 cell.