AW: Daten in Matrix suchen
27.09.2022 14:45:30
Daniel
Hi
wenns eine reine VBA-Lösung sein soll, dann beispielsweise über das Dictionary-Objekt.
Sub test()
Dim arrQ, arrZ
Dim dic
Dim z As Long, s As Long
Dim ID As String
arrQ = Sheets("Daten").Cells(24, 1).CurrentRegion.Value
arrZ = Sheets("Tabelle1").Cells(1, 1).CurrentRegion.Value
Set dic = CreateObject("scripting.dictionary")
For z = 2 To UBound(arrQ, 1)
For s = 2 To UBound(arrQ, 2)
ID = arrQ(z, 1) & "|" & arrQ(1, s)
If arrQ(z, s) = "" Then
dic(ID) = 0
Else
dic(ID) = arrQ(z, s)
End If
Next
Next
For z = 2 To UBound(arrZ, 1)
For s = 2 To UBound(arrZ, 2)
ID = arrZ(z, 1) & "|" & arrZ(1, s)
If dic.exists(ID) Then
arrZ(z, s) = dic(ID)
Else
arrZ(z, s) = "---"
End If
Next
Next
Sheets("Tabelle1").Cells(1, 1).CurrentRegion.Value = arrZ
End Sub
Gruß Daniel