800 Aufrufe
Gefragt in Tabellenkalkulation von
Guten Tag,

ich benötige ihre Hilfe bei der Erstellung einer drei stelligen Abfolge aus Zahlen und Buchstaben (ohne Null)

AB3, 1UZ oder zum Beispiel G4E

Keine der Abfolgen soll doppelt vorkommen und die Abfolgen sollen sich nachträglich nicht verändern lassen.

Geplant ist damit ein Formular auszudrucken und deshalb muss ersichtlich sein welche Nummern im Umlauf sind. Es würde reichen wenn man ein Tabellenblatt mit zum Beispiel 100 dieser Abfolgen ausgibt und danach 100 weiter erstellt wenn man diese benötigt.

Alternativ könnte ich mir auch vorstellen das man zum Beispiel 20 Abfolgen erstellt diese dann selbstständig in das Formular (auch Excel) einträgt und jeder Ausdruck eine andere Abfolge bekommt. Ob das Oberhaupt möglich ist weiß ich nicht.

Ich habe bereits mit Index und Zufallszahl sowie Zeichen(Zufallsbereich) experimentiert jedoch werden die bereits erstellten Abfolgen permanent geändert was nicht sein darf.

Ich bedanke mich recht herzlich.
fat61

18 Antworten

0 Punkte
Beantwortet von

Hallo fat61

Hast du VBA Kenntnisse Variablen/Array's/Solver ?

Gruß Michael

0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)

Hallo fat61

Hier schon mal ein Anfang!

Leicht gestrickt!

Spalte A 

1-9 und A-Z  oder beliebige Zeichen

Anzahl Zeichen beliebig

Ausgabe der Kombinationen Spalte B

Alle Kombinationen (mit 3 Zeichen) 

Bei Vergabe eines Codes würde ich in Spalte C ein X setzen z.b.

Gruß Nighty

Sub Kombi()
Dim Lzeile As Long, ZeilenIndex As Long
Dim T1 As Long, T2 As Long, T3 As Long, ZweiteDim As Long
Dim ErsteDim As Integer
Lzeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row - 1
ReDim Dat(1 To 3, 1 To Lzeile) As Variant
ZeilenIndex = 1
For ErsteDim = 1 To 3
 For ZweiteDim = 1 To Lzeile
  Dat(ErsteDim, ZweiteDim) = Cells(ZweiteDim + 1, 1)
 Next ZweiteDim
Next ErsteDim
For T1 = 1 To Lzeile
 For T2 = 1 To Lzeile
  For T3 = 1 To Lzeile
   ZeilenIndex = ZeilenIndex + 1
   Cells(ZeilenIndex, 2) = Dat(1, T1) & Dat(2, T2) & Dat(3, T3)
  Next T3
 Next T2
Next T1
End Sub

0 Punkte
Beantwortet von
Ja VBA Kenntnisse ein paar, das war auch mein ursprünglicher Plan. Jedoch bin ich mit VBA noch nicht so weit selbstständig Makros zu programmieren.
0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)

Hallo fat61

Dann öfne mal den VB Editor und füge das Makro in ein allgemeines Modul ein.

Nach Ausführung hast du über 50 000 Kombinationen bei ca 37 Zeichen

Das Makro ist Dynamisch,daher kannst du beliebig viele Zeichen eingeben(Pro zeile in Spalte A 1 Zeichen),es geht auch 100 oder 200 Zeichen,wie es dir gefaellt!

Erste Zeile aber Überschriften!

Gruß Nighty

0 Punkte
Beantwortet von

Hallo fat61,

hier mal mein bescheidener Vorschlag zum Thema. Verstehe ich das richtig, dass der Code immer aus 2 Buchstaben und einer Zahl besteht? Das schränkt die Anzahl der Möglichkeiten auf 18252 ein. Aber du willst ja nur 100. Führe dazu den folgenden Code in einem allgemeinen Modul aus.

Sub CodeErzeugen()

Randomize Timer

Max = 18252

For z = 1 To 100
  If Application.CountA(Range("A:A")) = Max Then Exit For
  Start = Timer
  Do
    If Timer > Start + 30 Then Exit Sub
    code = "   "
    For i = 1 To 2
      Do
        p = Int(Rnd * 3) + 1
      Loop Until Mid(code, p, 1) = " "
      zchn = Int(Rnd * 26) + 65
      Mid(code, p, 1) = Chr(zchn)
    Next i
    p = InStr(1, code, " ")
    zahl = Int(Rnd * 9) + 1
    Mid(code, p, 1) = CStr(zahl)
  Loop Until IsError(Application.Match(code, Range("A:A"), 0))
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = code
Next z

MsgBox "Geschafft"

End Sub

Oder hast kann es auch mal vorkommen, dass der Code aus 3 Buchstaben oder 3 Ziffern besteht? Dann hast du immerhin 42875 Möglichkeiten. In diesem Fall führe diesen Code aus:

Sub CodeErzeugen2()

Randomize Timer

Max = 42875

Range("A:A").NumberFormat = "@"

For z = 1 To 100
  If Application.CountA(Range("A:A")) = Max Then Exit For
  Start = Timer
  Do
    If Timer > Start + 30 Then Exit Sub
    code = ""
    For p = 1 To 3
      Do
        zchn = Int(Rnd * 42) + 49
      Loop Until Chr(zchn) Like "[A-Z]" Or Chr(zchn) Like "[1-9]"
      code = code & Chr(zchn)
    Next p
  Loop Until IsError(Application.Match(code, Range("A:A"), 0))
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = code
Next z

MsgBox "Geschafft"

End Sub

Gruß Mr. K.

0 Punkte
Beantwortet von
Hallo,

vielen dank, der code darf an jeder stelle eine Zahl haben also vom 111 bis hin zu ZZZ darf alles dabei sein. Nur keine Duplikate.

Ich hatte mir folgende Varianten überlegt:

1. ein Tabellenblatt voller Codes und man markiert diese farbig wenn man das Formular mit dem code ausgedruckt hat

2. man erstellt immer nur so viele codes wie man formulare drucken möchte, sprich alle codes die im Tabellenblatt stehen wurden bereits gedruckt

3. (keine Ahnung ob das geht) man sagt man druck 10 formulare und er druckt jedes mit anderem code und speichert die gedruckten codes selbstständig in ein Tabellenblatt (ggf. Mit Datum)

Aber jetzt probier ich erstmal was ihr mir geschickt habt. danke!
0 Punkte
Beantwortet von
Bearbeitet
@nighty ich habe dein Makro gerade erfolgreich getestet, jedoch ist mir aufgefallen das die Codes nach der Reihenfolge gelistet sind wie auch die Buchstaben bzw Ziffern in Spalte A gelistet sind

@xlKing CodeErzeugen2 funktioniert super, prüft das Makro auf Duplikate? und wäre es möglich das er jedes mal wenn ich 100 bzw 25 neue Codes erzeuge er das in eine weitere Spalte schreibt? Das man z.B. in Zelle A1 1.-22.Aug20 und dann kommen die Codes und dann in B1 23.-

Wenn ich jetzt codes bereits verwenden würde und ich würde die codes manuell in Spalte A eintragen, prüft er die dann auch auf Duplikate?
0 Punkte
Beantwortet von

Hallo fat61

Ja, ja und Ja. Es wird ein Zufallscode erzeugt. Ist dieser bereits vorhanden, so wird ein anderer Code erzeugt. So werden Dubletten vermieden. Es wird geprüft ob sich der Code bereits auf dem Blatt befindet, dabei ist es irrelevant, ob der Code von dir oder vom Makro erzeugt wurde. Allerdings funktioniert Code2 nur mit einer Spalte (hier SpalteA). Wenn du das auf  mehrere Spalten verteilt haben willst, brauchst du diesen Code:

Option Explicit
Sub CodeErzeugen3()

Dim Max As Long, Spalte As Long, z As Long, Start As Single, p As Byte
Dim code As String, zchn As String, anzahl As Long
Dim Codepos As Range, Codesheet As Worksheet, c As Range

Randomize Timer
Max = 42875

Sheets("Formular").Select

Set Codepos = Sheets("Formular").Range("B1") 'wo im Formular soll der Code stehen?
Set Codesheet = Sheets("AllUsedCodes") 'wie heißt das Blatt, wo alle bereits
                                       'verwendeten Codes aufgelistet sind?
Do 'Abfrage nach Anzahl
anzahl = Val(InputBox("Wie viele Formulare wollen Sie heute drucken"))
Loop Until CLng(anzahl) >= 0

'Spalte ermitteln
Set c = Codesheet.Cells.Find(Date)
If c Is Nothing Then
  Spalte = Codesheet.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
  Codesheet.Cells(1, Spalte) = Date
Else
  Spalte = c.Column
End If

For z = 1 To anzahl
  If Application.CountA(Codesheet.UsedRange) = Max Then Exit For
  Start = Timer
  Do
    If Timer > Start + 30 Then Exit Sub 'Timeout wenn nach 30 Sek. noch
                                        'kein unbenutzter Code gefunden wurde
    code = "" 'Code erzeugen
    For p = 1 To 3
      Do
        zchn = Int(Rnd * 42) + 49
      Loop Until Chr(zchn) Like "[A-Z]" Or Chr(zchn) Like "[1-9]"
      code = code & Chr(zchn)
    Next p
  Loop Until Codesheet.UsedRange.Find(code) Is Nothing 'Dubletten haben keine Chance
  
  With Codesheet.Cells(Rows.Count, Spalte).End(xlUp).Offset(1, 0)
   .NumberFormat = "@"
   .Value = code
  End With
  Codepos.Value = code
  Sheets("Formular").PrintOut
Next z

MsgBox "Geschafft"

End Sub

Der Code fragt zunächst die Anzahl der Drucke ab. Jeder Druck mit einem anderen Code. Dann wird die Spalte ermittelt in der die Codes eingetragen werden. Dann wird der Code erzeugt und das gesamte Blatt überprüft ob dieser Code bereits irgendwo vorhanden ist. Wenn ja wird einfach ein neuer Code erzeugt und zwar solange, bis man einen gültigen unbenutzten Code hat. Einziger Nachteil: Je näher du der Obergrenze von 42875 Möglichkeiten kommst, umso länger dauert es, einen nicht benutzten Code zu finden. Deshalb hab ich einen Timeout eingebaut, der nach 30 Sekunden ungültigen Code-Erzeugens Schluss macht. Den kannst du aber auch erhöhen, wenn er zu früh aufgibt. 

Außerdem habe ich gleich deinen Wunsch 3 aus der vorherigen Antwort eingearbeitet. Die Parameter des Printout-Befehls kannst du bei Wunsch mit dem Makrorekorder aufzeichnen und ergänzen.

Gruß Mr. K.

0 Punkte
Beantwortet von
Bearbeitet
@xlKing da bin ich jetzt platt. Echt heftig, vielen vielen Dank!

Ich habe jetzt in der Datei ein Modul erstellt und in dieses Modul das Skript (ich hoffe das heißt so) eingetragen und nach deinen Kommentaren verändert.

Ebenfalls habe ich einen Button erstellt auf dem man klicken kann, damit das Skript ausgeführt wird , jetzt druckt er mir die liste mit den Codes

Wenn ich das Skript richtig verstehe, wird ein Code erstellt und dann das Formular damit gedruckt. Ich brauche also keine Codes vorher in dem Tabellenblatt erstellen, im umkehrschluss heißt das wenn ich jetzt selbst schnell einen Code eintippe zum drucken erkennt er, was ich in dieses Tabellenblatt eingetragen hab und sieht das es eine Dublette ist falls er den selben Code erstellt.

Sie sind mir eine wahnsinns Hilfe!

Was ich noch ergänzen wollte, wenn die Formulare selbstständig gedruckt werden dann ist es auch nicht notwendig das man die in einzellne Spalten aufteilt. Diese Variante habe ich mir ausgedacht damit man einen besseren Überblick hat was gedruckt wurde. Wenn aber nun alle Codes in einer Spalte stehen ist das auch kein Problem denn wenn ein Formular zurück kommt, dann kann man es zum Beispiel über die Suchfunktion finden und ggf. farbig markieren.
0 Punkte
Beantwortet von

Hallo fat61

freut mich, dass er Ihnen gefällt. Wir sind hier im Forum aber alle per Du, also freut mich dass er dir gefällt :-)

jetzt druckt er mir die liste mit den Codes

Eigentlich sollte er nicht die Liste mit den Codes sondern das Formular drucken. und zwar jedesmal mit einem anderen Code. Dazu hab ich extra den Befehl Sheets("Formular").PrintOut eingebaut. Im Zweifel einfach direkt vor dem Druckbefehl nochmal Sheets("Formular").Select machen. Ist zwar eigentlich nicht nötig, aber vielleicht hilfts.

Du verstehst das Skript richtig. Der Code wird erstellt. Wenn er keine Dublette ist, wird er in das Formular und in die Liste eingetragen. Dann wird das Formular gedruckt und der nächste Code wird erstellt. Solange bis die gewünschte Anzahl an Formularen gedruckt wurde.

In der Liste haben wir jetzt eine Spaltenaufteilung. Und zwar eine Spalte pro Tag. Wenn du den Code am gleichen Tag mehrmals aufrufst, wird alles in die gleiche Spalte getragen. Für den nächsten Tag gibts dann eine neue Spalte. Du kannst deine bisherigen Codes aber auch in einer Spalte (z.B. Spalte A) zusammenfassen, unabhängig von den Tagen.

dann kann man es zum Beispiel über die Suchfunktion finden 

so prüft auch der Code auf Dubletten, er nutzt die Suchfunktion und sucht im gesamten Blatt. Ob die Codes dabei in mehreren Spalten stehen oder nur in einer ist dabei unerheblich. Wenn er nichts findet (Nothing) dann wird der Code verarbeitet.

PS: Welche Excel-Version hast du. Ab Excel 2007 oder höher ist die Spaltenaufteilung kein Problem. Da kannst du 16.384 mal drucken also umgerechnet fast 45 Jahre lang jeden Tag einmal. Bei früheren Excel Versionen ist aber schon nach 256 Spalten Schluss. Das reicht nicht mal für ein Jahr.

Wenn du weitere Fragen oder Änderungswünsche hast, kannst du dich gern nochmal melden.

Gruß Mr. K.

...