AW: Zellen aus mehreren Spalten sortieren
26.01.2021 14:58:24
Nepumuk
Hallo Katrin,
teste mal:
Option Explicit
Public Sub AdjustData()
Dim objCell As Range
Dim strFirstAddress As String
Dim vntItem As Variant, avntValues() As Variant
Dim lngRow As Long
Dim objDictionary As Object
Set objDictionary = CreateObject(Class:="Scripting.Dictionary")
With Worksheets("Tabelle1")
Redim avntValues(1 To .Cells(.Rows.Count, 1).End(xlUp).Row, 1 To 5)
For lngRow = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
avntValues(lngRow, 1) = .Cells(lngRow, 1).Value
objDictionary.Item(Key:=.Cells(lngRow, 1).Address) = vbNullString
Next
Set objCell = .Cells.Find(What:="Jahre", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
If objCell.Column > 1 Then
avntValues(objCell.Row, 2) = objCell.Value
objDictionary.Item(Key:=objCell.Address) = vbNullString
End If
Set objCell = .Cells.FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
Else
Call MsgBox("Suchbegriff ''Jahre'' nicht gefunden.", vbCritical, "Programmabbruch")
Exit Sub
End If
Set objCell = .Cells.Find(What:="cm", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
If objCell.Column > 1 Then
avntValues(objCell.Row, 3) = objCell.Value
objDictionary.Item(Key:=objCell.Address) = vbNullString
End If
Set objCell = .Cells.FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
Else
Call MsgBox("Suchbegriff ''cm'' nicht gefunden.", vbCritical, "Programmabbruch")
Exit Sub
End If
For Each vntItem In Array("Blond", "Rot", "Braun", "Grau")
Set objCell = .Cells.Find(What:=vntItem, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
If objCell.Column > 1 Then
avntValues(objCell.Row, 4) = objCell.Value
objDictionary.Item(Key:=objCell.Address) = vbNullString
End If
Set objCell = .Cells.FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
End If
Next
For Each objCell In .Range(.Cells(1, 2), .Cells(.Rows.Count, 5).End(xlUp))
If Not objDictionary.Exists(Key:=objCell.Address) Then _
avntValues(objCell.Row, 5) = objCell.Value
Next
.Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 5).Value = avntValues
End With
Set objDictionary = Nothing
End Sub
Gruß
Nepumuk