Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Wert in Tabelle suchen Listbox

Betrifft: Wert in Tabelle suchen Listbox von: Peter
Geschrieben am: 07.10.2020 10:41:17

Hallo,
ich habe eine ListBox1, die ich mit nachstehendem Code befülle:

Sub ListBox1_fuellen()
  Dim lRow As Long
  Dim lastRow As Long
  Dim objList As Object
  
  Dim wsName As String
  
  wsName = UserForm2.ComboBox1.Value
  'Debug.Print wsName
  
  Set objList = CreateObject("Scripting.dictionary")
  
  UserForm2.ListBox1.Clear
  UserForm2.ListBox1.ColumnCount = 11
  UserForm2.ListBox1.ColumnWidths = "3,3cm;4,0cm;2,0cm;3,0cm;3,5cm;3,0cm;3,0cm;5,5cm;4,8cm;2, _
1cm;2,3cm"
''                                   A     B     C     D     E     F     G     H     I     J     _
 K     L     M     N     O

  With Sheets(wsName)
    lastRow = .Range("A" & Sheets(wsName).Rows.Count).End(xlUp).Row

    For lRow = 2 To lastRow


      objList(lRow) = Array( _
        .Cells(lRow, 1).Value, _
        .Cells(lRow, 2).Value, _
        .Cells(lRow, 3).Value, _
        .Cells(lRow, 4).Value, _
        .Cells(lRow, 5).Value, _
        .Cells(lRow, 6).Value, _
        .Cells(lRow, 7).Value, _
        .Cells(lRow, 8).Value, _
        .Cells(lRow, 9).Value, _
        .Cells(lRow, 10).Value, _
        Cells(lRow, 11).Value)
    Next
  End With
  ListBox1.List = WorksheetFunction.Transpose(WorksheetFunction.Transpose(objList.items))
End Sub
Ich möchte aber gerne über die TextBox1 einen Suchbegriff eingeben und mittels Commandbutton1 die ListBox1 mit den gefundenen Werten befüllen. Hierzu habe ich bisher das nachstehende Makro:
Private Sub CommandButton1_Click()
    Dim c As Range
    Dim rngBereich As Range
    Dim lngAnzahl As Long
    Dim strFirst As String
    Dim txtSearch As String
    
    
    txtSearch = Me.TextBox1 'Suchbegriff
    
    
    Me.ListBox1.Clear
    Me.ListBox1.ColumnCount = 11
   
    
    With Sheets("Tabelle1")
        Set rngBereich = .Columns("A:K")
        Set c = rngBereich.Find(txtSearch, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            strFirst = c.Address
            Do
             
            
                ListBox1.AddItem .Cells(c.Row, 1)
                lngAnzahl = ListBox1.ListCount
                ListBox1.List(lngAnzahl - 1, 1) = .Cells(c.Row, 2)
                ListBox1.List(lngAnzahl - 1, 2) = .Cells(c.Row, 3)
                ListBox1.List(lngAnzahl - 1, 3) = .Cells(c.Row, 4)
                ListBox1.List(lngAnzahl - 1, 4) = .Cells(c.Row, 5)
                ListBox1.List(lngAnzahl - 1, 5) = .Cells(c.Row, 6)
                ListBox1.List(lngAnzahl - 1, 6) = .Cells(c.Row, 7)
                ListBox1.List(lngAnzahl - 1, 7) = .Cells(c.Row, 8)
                ListBox1.List(lngAnzahl - 1, 8) = .Cells(c.Row, 9)
                ListBox1.List(lngAnzahl - 1, 9) = .Cells(c.Row, 10)
'                ListBox1.List(lngAnzahl - 1, 10) = .Cells(c.Row, 11)    'bei AddItem 11 Spalte  _
nicht möglich
                
                Set c = rngBereich.FindNext(c)
            Loop While Not c Is Nothing And c.Address <> strFirst
        End If
    End With
End Sub
Könnt ihr mir bitte helfen, dass das Suchen mit mehr als 10 Spalten in Listbox möglich ist. Vielleicht kann man die beiden Makros irgendwie verbinden. Besten Dank für eure Hilfe.

Gruss
Peter

Betrifft: AW: Wert in Tabelle suchen Listbox
von: Nepumuk
Geschrieben am: 07.10.2020 11:19:30

Hallo Peter,

teste mal:

Private Sub CommandButton1_Click()
    
    Dim objCell As Range
    Dim strFirstAddress As String
    Dim avntValues() As Variant
    Dim ialngIndex As Long
    
    Call ListBox1.Clear
    ListBox1.ColumnCount = 11
    
    With Worksheets("Tabelle1")
        
        Set objCell = .Columns("A:K").Find(What:=TextBox1.Text, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        
        If Not objCell Is Nothing Then
            
            strFirstAddress = objCell.Address
            
            Do
                
                Redim Preserve avntValues(10, ialngIndex)
                
                avntValues(0, ialngIndex) = .Cells(objCell.Row, 1)
                avntValues(1, ialngIndex) = .Cells(objCell.Row, 2)
                avntValues(2, ialngIndex) = .Cells(objCell.Row, 3)
                avntValues(3, ialngIndex) = .Cells(objCell.Row, 4)
                avntValues(4, ialngIndex) = .Cells(objCell.Row, 5)
                avntValues(5, ialngIndex) = .Cells(objCell.Row, 6)
                avntValues(6, ialngIndex) = .Cells(objCell.Row, 7)
                avntValues(7, ialngIndex) = .Cells(objCell.Row, 8)
                avntValues(8, ialngIndex) = .Cells(objCell.Row, 9)
                avntValues(9, ialngIndex) = .Cells(objCell.Row, 10)
                avntValues(10, ialngIndex) = .Cells(objCell.Row, 11)
                
                ialngIndex = ialngIndex + 1
                
                Set objCell = .Columns("A:K").FindNext(After:=objCell)
                
            Loop Until objCell.Address = strFirstAddress
        End If
    End With
    
    If ialngIndex > 0 Then ListBox1.Column = avntValues
    
End Sub

Gruß
Nepumuk

Betrifft: AW: Wert in Tabelle suchen Listbox
von: Peter
Geschrieben am: 07.10.2020 11:24:04

Hallo Nepumuk,
vielen, vielen Dank. Es funktioniert einwandfrei.

Wünsche Dir noch einen schönen Tag.

Gruss
Peter

Betrifft: AW: Wert in Tabelle suchen Listbox
von: Peter
Geschrieben am: 07.10.2020 18:27:13

Hallo Nepumuk,
das von Dir gefertigte Makro funktioniert einwandfrei.

Jetzt habe ich jedoch folgendes Problem:

Wenn ich den gefundenen Wert bzw. die gefundene Zeile auswähle, durch markieren, wird nicht die Zeile übertragen in die Textboxes sondern der Listindex bzw. die Zeilennummer der ListBox.

Was muss ich ändern, damit der gefundene Wert entsprechend der Zeile übertragen wird und dann auch eine Änderung erfolgen kann?

Beispiel: gesucht ein Wert welcher in Tabelle in Zeile 10 steht und in der ListBox als Eintrag an 1. Stelle steht.

Gruss
Peter

Betrifft: AW: Wert in Tabelle suchen Listbox
von: Nepumuk
Geschrieben am: 07.10.2020 18:42:54

Hallo Peter,

na dann speichern wir einfach die Zeilennummer in der letzten (12.) Spalte:

Private Sub ListBox1_Click()
    With ListBox1
        MsgBox .List(.ListIndex, 11)
    End With
End Sub

Private Sub CommandButton1_Click()
    
    Dim objCell As Range
    Dim strFirstAddress As String
    Dim avntValues() As Variant
    Dim ialngIndex As Long
    
    Call ListBox1.Clear
    ListBox1.ColumnCount = 12
    
    With Worksheets("Tabelle1")
        
        Set objCell = .Columns("A:K").Find(What:=TextBox1.Text, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        
        If Not objCell Is Nothing Then
            
            strFirstAddress = objCell.Address
            
            Do
                
                Redim Preserve avntValues(11, ialngIndex)
                
                avntValues(0, ialngIndex) = .Cells(objCell.Row, 1)
                avntValues(1, ialngIndex) = .Cells(objCell.Row, 2)
                avntValues(2, ialngIndex) = .Cells(objCell.Row, 3)
                avntValues(3, ialngIndex) = .Cells(objCell.Row, 4)
                avntValues(4, ialngIndex) = .Cells(objCell.Row, 5)
                avntValues(5, ialngIndex) = .Cells(objCell.Row, 6)
                avntValues(6, ialngIndex) = .Cells(objCell.Row, 7)
                avntValues(7, ialngIndex) = .Cells(objCell.Row, 8)
                avntValues(8, ialngIndex) = .Cells(objCell.Row, 9)
                avntValues(9, ialngIndex) = .Cells(objCell.Row, 10)
                avntValues(10, ialngIndex) = .Cells(objCell.Row, 11)
                avntValues(11, ialngIndex) = objCell.Row
                
                ialngIndex = ialngIndex + 1
                
                Set objCell = .Columns("A:K").FindNext(After:=objCell)
                
            Loop Until objCell.Address = strFirstAddress
            
            Set objCell = Nothing
            
        End If
    End With
    
    If ialngIndex > 0 Then ListBox1.Column = avntValues
    
End Sub

Das Event "ListBox1_Click" zeigt dir wie du an die Zeile kommst.

Gruß
Nepumuk

Betrifft: AW: Wert in Tabelle suchen Listbox
von: Peter
Geschrieben am: 07.10.2020 18:48:43

Hallo Nepumuk,
besten Dank und noch einen schönen Abend.

Gruss
Peter