AW: VBA: Spalten nach Wert suchen
12.09.2017 13:28:27
Rudi
Hallo,
sowas?
Sub BK()
Dim vArr, i As Long, j As Long, objCount As Object, oObj
Dim arrOut(1 To 3, 1 To 2), iMax As Integer, strOut As String
vArr = Cells(1, 1).CurrentRegion
Set objCount = CreateObject("scripting.dictionary")
For i = 2 To UBound(vArr)
For j = 2 To 12 Step 2
objCount(vArr(i, j)) = objCount(vArr(i, j)) + 1
Next j
Next i
For i = 1 To 3
iMax = WorksheetFunction.Max(objCount.items)
For Each oObj In objCount
If objCount(oObj) = iMax Then
arrOut(i, 1) = iMax & "x: "
arrOut(i, 2) = arrOut(i, 2) & "; " & oObj
objCount.Remove oObj
End If
Next oObj
Next i
For i = 1 To 3
strOut = strOut & arrOut(i, 1) & Mid(arrOut(i, 2), 3) & vbLf
Next i
MsgBox strOut
End Sub
Gruß
Rudi