AW: Werte aus mehreren TB in Tabelle suchen
06.06.2019 08:36:45
Mullit
Hallo,
im Prinzip geht sowas:
Option Explicit
Private Sub CommandButton1_Click()
Dim objRange As Range, objUnion As Range, objCell As Range
Dim avntControlValues() As Variant
Dim ialngIndex As Long
Dim strFirstAddress As String
With Tabelle2
Set objRange = .Range(.Cells(2, 19), .Cells(.Cells(.Rows.Count, 19).End(xlUp).Row, 25))
End With
avntControlValues = Array(TextBox1.Value, TextBox2.Value, TextBox4.Value, _
TextBox5.Value, TextBox6.Value, TextBox7.Value, ComboBox1.Value)
For ialngIndex = 0 To Ubound(avntControlValues)
If avntControlValues(ialngIndex) <> vbNullString Then
With objRange
Set objCell = .Find(What:=avntControlValues(ialngIndex), _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
If objUnion Is Nothing Then
Set objUnion = objCell
Else
Set objUnion = Union(objUnion, objCell)
End If
Set objCell = .FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
End If
End With
End If
Next
If objUnion Is Nothing Then
Call MsgBox("Keine Werte gefunden...!", vbExclamation)
Else
With objUnion
Call .Parent.Activate
Call .Select
End With
Set objUnion = Nothing
End If
Set objCell = Nothing
Set objRange = Nothing
End Sub
VBA/HTML - CodeConverter für Office-Foren
AddIn für Excel/Word 2000-2010 - komplett in VBA geschrieben von Lukas Mosimann
Projektbetreuung durch mumpel
Code erstellt und getestet in Office 12
Gruß, Mullit