AW: Matrix mit 4 Variablen durchsuchen
13.05.2023 23:30:56
Pappawinni
Bin nochmal über dieses Makro gestolpert, hab es jetzt einfach irgendwie fertig gemacht,
inwieweit das die Anforderungen erfüllt, kann ich nicht sagen, greift jedenfalls nicht auf das Blatt Referenz, sondern auf Basis...
und ist unkommentiert...
Option Explicit
Public Sub TP_auswerten()
Dim strTechnischerPlatz As String
Dim i As Long, lngLastRow As Long, lngLine As Long
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim aSearch() As Variant
Dim rngHierarchy As Range
Dim rngRef As Range, rngTarget As Range, oCell As Range, oCol As Range, rngOutCol As Range
Dim rngFoundTP As Range
Dim strS As String
Set wks1 = ThisWorkbook.Worksheets("Matrix_Auswerten")
Set wks2 = ThisWorkbook.Worksheets("Basis")
Set rngHierarchy = wks2.Range("I3:DY7")
Set rngRef = wks2.Range("E11:E40")
Set rngTarget = wks1.Range("C4:AA4")
lngLastRow = wks1.Cells(Rows.Count, 1).End(xlUp).row
For lngLine = 6 To lngLastRow
aSearch = Array()
strTechnischerPlatz = wks1.Cells(lngLine, 1).Value
Select Case Len(strTechnischerPlatz)
Case 26
aSearch = Array(Left(strTechnischerPlatz, 7), _
Mid(strTechnischerPlatz, 8, 2), _
Mid(strTechnischerPlatz, 10, 7), _
Mid(strTechnischerPlatz, 17, 5), _
Right(strTechnischerPlatz, 5))
Case 21
aSearch = Array(Left(strTechnischerPlatz, 7), _
Mid(strTechnischerPlatz, 8, 2), _
Mid(strTechnischerPlatz, 10, 7), _
Right(strTechnischerPlatz, 5))
Case 16
aSearch = Array(Left(strTechnischerPlatz, 7), _
Mid(strTechnischerPlatz, 8, 2), _
Right(strTechnischerPlatz, 7))
Case 9
aSearch = Array(Left(strTechnischerPlatz, 7), _
Right(strTechnischerPlatz, 2))
Case 7
aSearch = Array(strTechnischerPlatz)
Case Else
aSearch = Array()
MsgBox "Zeile " & lngLine & vbCrLf & _
"fehlerhafter Technischer Platz"
End Select
Set rngFoundTP = HierarchicalSearch_SAP_TP(rngHierarchy, aSearch)
rngTarget.Offset(lngLine - rngTarget.row).ClearContents
If Not (rngFoundTP Is Nothing) Then
For Each oCol In Range(rngFoundTP.Cells(1, 1), rngFoundTP.Cells(1, rngFoundTP.Columns.Count))
i = 0
For Each oCell In rngRef.Offset(0, oCol.Column - rngRef.Column)
i = i + 1
If oCell.Value = "A" Then
strS = rngRef(i, 1)
aSearch = Array(strS)
Set rngOutCol = HierarchicalSearch_SAP_TP(rngTarget, aSearch)
If Not (rngOutCol Is Nothing) Then
wks1.Cells(lngLine, rngOutCol.Column).Value = "A"
End If
End If
Next
Next
End If
Next
End Sub
Private Function HierarchicalSearch_SAP_TP(searchRange As Range, searchTerms() As Variant) As Range
Dim rngResult As Range
Dim oCell As Range
Dim lCol As Long
Dim IndexSearchTerm As Long
Dim strLike As String
Dim bolfound As Boolean
Dim iA As Long, iB As Long
Set rngResult = Range(searchRange.Cells(1, 1), searchRange.Cells(UBound(searchTerms) + 1, searchRange.Columns.Count))
bolfound = False
For IndexSearchTerm = 0 To UBound(searchTerms)
iA = 0: iB = 0
For lCol = 1 To rngResult.Columns.Count
Set oCell = rngResult.Cells(IndexSearchTerm + 1, lCol)
strLike = Replace(oCell.Value, "+", "?")
bolfound = searchTerms(IndexSearchTerm) Like strLike
iA = IIf(bolfound And (iA = 0), lCol, iA)
If oCell.MergeCells Then lCol = lCol - 1 + oCell.MergeArea.Columns.Count
iB = IIf(bolfound And (iA > 0), lCol, iB)
If (Not bolfound) And (iA > 0) Then Exit For
Next
If (iA > 0) And (iB >= iA) Then
Set rngResult = Range(rngResult.Cells(1, iA), rngResult.Cells(rngResult.Rows.Count, iB))
Else
Set rngResult = Nothing
Exit For
End If
Next
Set HierarchicalSearch_SAP_TP = rngResult
End Function