Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1800to1804
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Comboboxinhalt suchen und kopieren
04.01.2021 14:45:36
Dani
Hallo Zusammen
Ich stehe bei einem VBA Code an und benötige etwas Unterstützung.
Folgendes möchte ich machen:
Ich habe eine Userform erstellt bei der ich mittels Combobox per Dropdownliste eine Nummer auswählen kann. Mit Klick auf den Commandbutton soll der Inhalt der Combobox in der Tabelle 1 gesucht werden und anschliessend einige (nicht alle) Zellen der Zeile in das Tabellenblatt 2 kopiert werden. Dabei sollen die Einträge jeweils darunter eingefügt werden.
Ich habe dafür mittels Buch und Internetrecherchen entsprechend ein Code zusammengebastelt:
  • 
    Private Sub CommandButton2_Click()
    Dim Wss, Wsz As Worksheet
    Dim Bns, Bnz As Range 'Benutzter Bereich Start- und Zieltabelle
    Dim Suche As Variant
    Dim StartZeile
    Set Bns = Wss.Cells(Rows.Count, 1).End(xlUp).Row 'Benutzter Bereich Starttabelle
    Set Bnz = Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1 'Benutzter Bereich Zieltabelle
    Set Wss = Tabelle1 'Starttabelle
    Set Wsz = Tabelle2 'Zieltabelle
    If ComboBox1 = Empty Then MsgBox "Nummer wählen": Exit Sub
    If ComboBox1 = Not Empty Then
    Suche = Bns.Find(ComboBox1)
    Wsz.Cells(StartZeile, 1) = Suche
    Wsz.Cells(StartZeile, 1) = Suche.Offset(0, 1)
    End If
    End Sub
    

  • Ich bin noch bescheiden unterwegs daher kann es gut sein, dass ich das Ganze komplett falsch angegangen bin. (Hoffe jedoch nicht). Was funktioniert ist die Msg Box wenn kein Inhalt ausgewählt wurde. Beim Testen wird bei mir die erste "Set" Zeile Gelb markiert, wieso weiss ich nicht.
    Folgende Fragen habe ich dazu:
    1. Bin ich komplett auf dem falschen Weg?
    2. Wieso wird die angesprochene gelb markierte Zeile markiert (Wo liegt der Fehler)?
    3. Gibt es sonst noch etwas, was Falsch sein könnte bzw. vor allem auch was vereinfacht/verbessert werden kann?
    (4. Falls Ihr ein gutes VBA Buch kennt wäre ich auch diesbezüglich noch dankbar.)
    Vielen Dank für Eure Rückmeldungen.
    Gruss Dani

    19
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 15:20:50
    ChrisL
    Hi Dani
    Sieht nicht schlecht aus.
    Du kannst Wss und Wsz erst verwenden, nachdem du es definiert hast. Also umdrehen.
    Weiter versuchst du Row (Ganzzahl, Typ Long) in eine Range Variable zu quetschen.
    Bns und Bnz ergibt noch keinen Sinn, aber fehlerfrei...
    Set Wss = Tabelle1 'Starttabelle
    Set Wsz = Tabelle2 'Zieltabelle
    Set Bns = Wss.Cells(Rows.Count, 1).End(xlUp) 'Benutzter Bereich Starttabelle
    Set Bnz = Wsz.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'Benutzter Bereich Zieltabelle
    
    Da du bei Wss keine Angabe machst, würde die Variable als Variant dimensioniert. Darum vervollständigen (dito für Bns):
    Dim Wss As Worksheet, Wsz As Worksheet
    
    Welcher Bereich soll durchsucht werden (nur Spalte A?) und was sollte danach geschehen?
    cu
    Chris
    Anzeige
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 16:07:50
    Dani
    Hallo Chris
    Vielen Dank für die sehr schnelle Rückmeldung. Das mit dem umgekehrt macht bei genauerem überlegen natürlich Sinn, ebenso das mit Wss welches ich nicht definiert habe... xD
    Bezüglich Bns und Bnz: Du hast da das "Row" entfernt. Deine Aussage bezüglich Ganzzahl etc. klingt plausibel. Ich möchte das verstehen darum folgendes Bsp. welches nichts mit meinem Code zu tun hat: (Es geht mir nur um die Definierung "IngZeileMax". Bei dieser ist das "Row" wieder ersichtlich. Ist dies auf die Deklaration "Dim IngZeileMax as long" zu tun?(Im Zusammenhang mit Row als Ganzzahl, Typ Long?))
  • 
    Sub TabelleInDatenfeldPackenUndZurueck()
    Dim VarDat As Variant
    Dim lngZeile As Long
    Dim lngZeileMax As Long
    Dim lngSpalte As Long
    Dim lngSpalteMax As Long
    Dim dblWert As Double
    With Tabelle2
    VarDat = .UsedRange.Value
    lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
    lngSpalteMax = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For lngZeile = 1 To lngZeileMax
    For lngSpalte = 1 To lngSpalteMax
    dblWert = VarDat(lngZeile, lngSpalte)
    VarDat(lngZeile, lngSpalte) = dblWert * 2
    Next lngSpalte
    Next lngZeile
    .Range(.Cells(1, 1), _
    .Cells(lngZeileMax, lngSpalteMax)).Value = VarDat
    End With
    End Sub
    

  • Welcher Bereich soll durchsucht werden (nur Spalte A?) und was sollte danach geschehen?
    Ich habe keinen fixen Bereich. Konkrett handelt es sich um eine formatierte Tabelle welche laufen mit Einträgen ergänzt wird. Somit "kein" Ende besitzt. (Der Start ist aber der Zeile 4). Deshalb habe ich "Bns" definiert um die Anzahl Einträge zu zählen und als Bereich zu definieren. Das selbe gilt für "Bnz" für das zweite Tabellenblatt bei dem ebenfalls vorhanden Einträge gezählt werden sollen. Am Ende sollen der per Combobox definierte Wert in der Tabelle 1 gesucht und in der Tabelle 2 (Mit zusätzlichen Zellen in der gleichen zuvor gefundenen Zeile) unten angefügt werden.
  • If ComboBox1 = Not Empty Then
    Suche = Bns.Find(ComboBox1)
    Wsz.Cells(StartZeile, 1) = Suche
    Wsz.Cells(StartZeile, 1) = Suche.Offset(0, 1)

  • Deshalb möchte ich per "Suche" (welche ich als variant definiert habe da auch Buchstaben vorkommen)mittels "Bns" als Bereich und dem Inhalt der Combobox vergleichen. Ich denke hier mache ich wieder etwas falsch.
    Vielen Dank für die bisherige Hilfe.
    Gruss Dani
    Anzeige
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 16:26:12
    ChrisL
    Hi Dani
    Zur Theorie (Teil 2 schaue ich mir nachher an).
    Ja korrekt. Weil hier lngZeileMax als Long deklariert wird, kann Row verwendet werden. Hier mal zwei Beispiele (Spalte A mit variabler Länge):
    Sub t1()
    Dim lngZeileMax As Long
    With Tabelle2
    lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
    MsgBox .Range(.Cells(1, 1), .Cells(lngZeileMax, 1)).Address
    MsgBox .Range("A1:A" & lngZeileMax).Address
    End With
    End Sub
    

    Sub t2()
    Dim rngZeileMax As Range
    With Tabelle2
    Set rngZeileMax = .Cells(.Rows.Count, 1).End(xlUp)
    MsgBox .Range(.Cells(1, 1), rngZeileMax).Address
    MsgBox .Range("A1:A" & rngZeileMax.Row).Address
    End With
    End Sub
    

    VarDat ist ein Array/Datenfeld. Obwohl sehr hilfreich, insbesondere um performante Lösungen zu erstellen, würde ich im jetzigen Stadium eher darauf verzichten (es sei denn, dir ist das Prinzip aus anderen Programmiersprachen bereits bekannt).
    cu
    Chris
    Anzeige
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 16:35:05
    ChrisL
    wie sieht es mit der Lösung von Ralf aus? Jetzt wo ich es mir noch einmal durchgelesen habe, würde ich wahrscheinlich auf eine ähnliche Lösung kommen.
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 16:45:34
    Dani
    Vielen Dank für die Erklärung der Theorie :)
    Die Lösung von Ralf hat noch nicht geklappt (Sieh Antwort darauf)
    Versuche es weiter und kontrolliere nochmals alles vielleicht habe ich igendwo was falsch eingefügt/getippt.
    Gruss Dani
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 17:04:28
    ralf_b
    Hallo Dani,
    sorry das real life.
    ich habe da noch einen Fehler entdeckt. bnz soll erste Einfügezelle in Spalte A sein
    Set Bnz = Wsz.Cells(Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) 'erste freie Zelle Zieltabelle
    gruß
    rb
    Anzeige
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 18:04:56
    Dani
    Hallo rb
    Das ist perfekt jetzt läuft der Code optimal. Vielen Dank dir und Chris :)
    Werde das morgen noch weiter an meine Bedürfnisse anpassen.
    Danke euch und Gruss
    Dani
    danke für die rückmeldung -owT
    04.01.2021 18:11:49
    ralf_b
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 15:25:15
    ralf_b
    Hallo Dani,
    erst das übergeordnete Objekt initialisieren
    dein benutzter Bereich ist nur eine Zeilennummer
    Suchergebnis wird in selbe Zelle eingefügt.
    da ich keine Testdaten habe, hier mal ein Versuch
    
    Private Sub CommandButton2_Click()
    Dim Wss As Worksheet, Wsz As Worksheet
    Dim Bns As Range 'benutzter bereich Suchtabelle
    Dim Bnz As Range 'erste freie zelle Zieltabelle
    Dim Suche As Range
    Dim StartZeile As Long
    Set Wss = Tabelle1 'Starttabelle
    Set Wsz = Tabelle2 'Zieltabelle
    StartZeile = Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set Bns = Wss.Cells(1, 1).Resize(StartZeile - 1) 'Benutzter Bereich Starttabelle
    Set Bnz = Wsz.Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) 'erste freie Zelle Zieltabelle
    If ComboBox1 = Empty Then MsgBox "Nummer wählen": Exit Sub
    Set Suche = Bns.Find(ComboBox1)
    If Not Suche Is Nothing Then
    Bnz = Suche
    Bnz.Offset(, 1) = Suche.Offset(0, 1)
    End If
    Set Wss = Nothing: Set Wsz = Nothing
    Set Bns = Nothing: Set Bnz = Nothing: Set Suche = Nothing
    End Sub
    

    gruß
    rb
    Anzeige
    AW: Comboboxinhalt suchen und kopieren
    04.01.2021 16:36:00
    Dani
    Hallo Rb
    Danke für die Rückmeldung habe deinen Code ausprobiert. Ich scheitere bei der ersten Zeile (unten) beim "x1Up" "Variable nicht definiert!" (Das hatte ich schon einmal. Was könnte die Ursache davon sein?
    StartZeile = Wsz.Cells(Rows.Count, 1).End(x1Up).Row + 1
    Set Bns = Wss.Cells(1, 1).Resize(StartZeile - 1) 'Benutzter Bereich Starttabelle
    Set Bnz = Wsz.Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) 'Erste freie Zelle Zieltabelle
    

    Ich habe versucht den Code zu verstehen. Bei der "StartZeile=Wsz..." müsste da nicht "Wss" stehen? Wsz ist meine Zieltabelle welche mit dem Bereich der Starttabelle nicht direkt etwas zu tun hat. (Zumidest was das Zählen angeht.) Fällt mir auf weil: "Set Bns = Wss.Cells(1, 1).Resize(StartZeile - 1)
    Gruss und Danke für die Hilfe
    Dani
    Anzeige
    XL nicht X1-23 owT.
    04.01.2021 16:44:58
    ChrisL
    .
    AW: XL nicht X1-23 owT.
    06.01.2021 20:16:00
    Dani
    Hallo Rb
    Entschuldige wenn ich diesen Post nochmals aufrufe. Ich habe noch ein Problem festgestellt und finde den Fehler nicht. Folgend nochmals mein vollständiger Code:
    Private Sub CommandButton1_Click()
    'Neu Offertnummer
    Range("Tabelle1[Offertnummer]").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Range("A4").Select
    Selection.AutoFill Destination:=Range("Tabelle1[Offertnummer]"), Type:= _
    xlFillDefault
    Unload UserForm1
    End Sub
    Private Sub CommandButton2_Click()
    Dim Wss As Worksheet, Wsz As Worksheet
    Dim Bns As Range 'benutzter bereich Suchtabelle
    Dim Bnz As Range 'erste freie zelle Zieltabelle
    Dim Suche As Range
    Dim StartZeile As Long
    Set Wss = Tabelle1 'Starttabelle
    Set Wsz = Tabelle2 'Zieltabelle
    StartZeile = Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set Bns = Wss.Cells(1, 1).Resize(StartZeile - 1) 'Benutzter Bereich Starttabelle
    Set Bnz = Wsz.Cells(Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) 'erste freie Zelle  _
    Zieltabelle
    If ComboBox1 = Empty Then MsgBox "Nummer wählen": Exit Sub
    Set Suche = Bns.Find(ComboBox1)
    If Not Suche Is Nothing Then
    Bnz.Offset(0, 5) = Suche
    Bnz.Offset(0, 1) = Suche.Offset(0, 1)
    Bnz.Offset(0, 2) = Suche.Offset(0, 2)
    Bnz.Offset(0, 4) = "Auftrag"
    End If
    Set Wss = Nothing: Set Wsz = Nothing
    Set Bns = Nothing: Set Bnz = Nothing: Set Suche = Nothing
    Unload UserForm1
    End Sub
    
    Das Problem ist das die Nummern nur der Reihe nach kopiert werden.
    Bsp. anhand der einer Liste
    20-001 (wird kopiert)
    20-001.1 (funktioniert)
    20-001.2 (wird nicht benötigt oder erst später kopiert)
    20-002 (soll kopiert werden geht aber nicht.)
    20-003 (und so weiter)
    Aus meiner Sicht vermute ich den Fehler bei folgender Code Stelle:
    StartZeile = Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set Bns = Wss.Cells(1, 1).Resize(StartZeile - 1) 'Benutzter Bereich Starttabelle
    
    StartZeile sucht die erste freie Spalte in meiner Zieltabelle.
    Bns bezieht sich auf meine Starttabelle welche jedoch "StartZeile" mit Bezug auf die Zieltabelle berücksichtig. Muss das so sein? Oder wo könnte sonst der Fehler sein?
    Habe schon diverses ausprobiert und weis leider nicht mehr weiter.
    Gruss und Danke
    Dani
    Anzeige
    AW: XL nicht X1-23 owT.
    06.01.2021 21:27:09
    ralf_b
    hast du das mal probiert?
     StartZeile = Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1
    StartZeile = Wss.Cells(Rows.Count, 1).End(xlUp).Row + 1
    

    AW: XL nicht X1-23 owT.
    06.01.2021 21:36:07
    Dani
    Hallo Ralf
    Ich habe alles versucht nur das nicht obwohl ich den Fehler am Richtigen Ort vermutete....
    Danke und Sorry für die Umstände
    Gruss
    Dani
    AW: XL nicht X1-23 owT.
    08.01.2021 20:13:22
    Dani
    Hallo Ralf
    Ich habe noch ein Problem welches ich nicht lösen kann. Ich habe den gesamten Code von dir an meine Bedürfnisse angepasst:
    
    Private Sub CommandButton2_Click() 'Projekt Erstellen
    Dim Wss As Worksheet, Wsz As Worksheet
    Dim Bns As Range 'benutzter bereich Starttabelle
    Dim Bnz As Range, Bnz2 As Range 'erste freie zelle Zieltabelle
    Dim Suche As Range, Suche2 As Range
    Dim StartZeile As Long, Startzeile2 As Long
    Dim z1, z2, a, b, Was, d
    Dim e As Integer, f As Integer
    Dim z3 As Long
    Set Wss = Tabelle1 'Starttabelle
    Set Wsz = Tabelle2 'Zieltabelle
    If ComboBox1 = Empty Then MsgBox "Nummer wählen": Exit Sub
    StartZeile = Wss.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Startzeile2 = Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set Bns = Wss.Cells(1, 1).Resize(StartZeile - 1) 'Benutzter Bereich Starttabelle
    Set Bnz = Wsz.Cells(Wsz.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) 'erste freie Zelle  _
    Zieltabelle
    Set Was = Bnz.Offset(-1, 0) 'Wert 1 Zellen oberhalb der aktuell ersten freien Zelle
    Set Suche = Bns.Find(ComboBox1) 'Sucht ob Comboboxinhalt in Starttabelle vorhanden
    If Not Suche Is Nothing Then 'falls gefunden
    Set Bnz2 = Wsz.Cells(6, 1).Resize(Startzeile2 - 1) 'Benutzter Bereich Zieltabelle
    Set Suche2 = Bnz2.Find(ComboBox1) 'Sucht ob Comboboxinhalt in Zieltabelle vorhanden
    If Not Suche2 Is Nothing Then MsgBox "Bereits weiter verarbeitet!": Wsz.Select 'falls gedfunden  _
    -> Msgbox
    Else
    Bnz.EntireRow.Insert 'Neue Zeile einfügen (an erster freie Zelle)
    Bnz.Offset(-1, 5) = Suche.Offset(0, 0)
    Bnz.Offset(-1, 1) = Suche.Offset(0, 1)
    Bnz.Offset(-1, 2) = Suche.Offset(0, 2)
    Bnz.Offset(-1, 4) = "Auftrag"
    End If
    z1 = Left(Was, 8)
    z2 = Left(Was, 5)
    z3 = Right(z1, 3)
    a = Right(Was, 2)
    b = Left(Was, 8)
    e = Len(Was)
    f = e + a
    If Len(ComboBox1)  98: a = z2 & "" & (z3 + 1)  'kombinieert bei Zahl über 99
    End Select
    Bnz.Offset(-1, 0) = a
    Else                        'Falls Zeichenlänge der Combobox höher (oder sonst)
    Select Case d
    Case e > 9: d = b & ".01" '
    Case f > 19: d = b & ".0" & a + 1
    Case f 
    Mein Problem ist, dass das Makro ausgeführt wird jedoch nichts gemacht wird. Zum Code. Ich habe einen Teil des Codes kopiert um eine zweite Suche zu starten (In der 6. Spalte auf dem Zielblatt).
    Wird dort der Wert (Suche2) gefunden soll die Msgbox "Bereits weiter verarbeitet!" ausgegeben werden und zum Ziel Blatt gesprungen werden. Der Rest des Codes habe ich anhand bestehender anderer Code welche tadellos funktioniert erstellt. (Ich benötigte teils die selben Funktionen). Das einzige bei dem ich mir unsicher bin ist ob die Kombination If, Case Else, Case klappt, bin aber der Meinung das dies gehen müsste.
    Was passiert? Eigentlich nichts. Wenn ich Einträge umwandle obwohl diese bereits vorhanden sind kommt keine MsgBox aber es wird auch kein neuer Eintrag erstellt. Ich vermute aufgrund dessen, dass der Fehler bei "Suche2","Bnz2" oder "Startzeile2 "liegt. Leider finde ich diesen nicht.
    Kannst du dir dies womöglich anschauen?
    Vielen Dank dir und Entschuldigung wenn ich nochmals darauf zurück komme.
    Beste Grüsse
    Dani
    Anzeige
    AW: XL nicht X1-23 owT.
    09.01.2021 00:10:44
    ralf_b
    Hallo Dani,
    deine Casebedingungen sehen seltsam aus. Hast du dich mal damit beschäftigt?
    
    select case d gibt die Variable d als Prüfelement vor.
    case e > 9   hat wohl kaum etwas mit d zu tun. oder? 
    
    im Übrigen sind deine Ratespiele nicht sehr zielführend. Ich empfehle dir dringend dich mit dem Debugging zu beschäftigen. Dann werden diese Unsicherheiten in deiner Fehlersuche sehr viel weniger.
    gruß
    rb
    AW: XL nicht X1-23 owT.
    09.01.2021 11:16:10
    Dani
    Hallo Ralf
    Danke für deine Rückmeldung. Du hast recht das Debuggen hat mir geholfen den Fehler zu finden. An den Case Methoden musste ich nichts verändern, sprich diese funktionierten.
    Danke für deinen Tipp
    Gruss Dani
    Anzeige
    AW: XL nicht X1-23 owT.
    07.01.2021 08:23:03
    ChrisL
    Hi
    Das Problem ist inzwischen gelöst, aber hier noch eine kleine Alternative. Für den Fall, dass auf Tabelle1!B:C mal die Werte angepasst werden.
    Wenn du die ComboBox-Eigenschaft MatchRequired auf True setzt und die Combobox mit gültigen Werten lädst, kannst du auf die zweite If-Prüfung verzichten.
    Private Sub CommandButton2_Click()
    If ComboBox1 = Empty Then MsgBox "Nummer wählen": Exit Sub
    If WorksheetFunction.CountIf(Tabelle1.Columns(1), ComboBox1) Then
    With Tabelle2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    .Value = ComboBox1
    .Offset(0, 1).Formula = "=VLOOKUP(" & .Address(0, 0) & ",Tabelle1!A:C,2)"
    .Offset(0, 2).Formula = "=VLOOKUP(" & .Address(0, 0) & ",Tabelle1!A:C,3)"
    .Offset(0, 3) = "Auftrag"
    End With
    End If
    End Sub
    
    cu
    Chris
    AW: XL nicht X1-23 owT.
    07.01.2021 19:29:31
    Dani
    Hallo Chris
    Danke dafür ich werde es mir noch anschauen. Aber vermutlich bleibe ich auf der momentanen Lösung da ich diese bereits ausgebaut und angepasst habe.
    Danke trotzdem :)
    Gruss Dani

    300 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Links zu Excel-Dialogen

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige