AW: Zeichen in einem String zählen
11.02.2011 11:12:14
Rudi
Hallo,
meine Vermutung:
du willst Summen nach Frühling, Herbst etc. bilden und dabei einen Status *open* berücksichtigen, oder?
Für den Code: Herbst etc in A, Staus in C, Umsatz in F
Sub xxx1()
Dim arrSum, i
arrSum = SumBrand(1, 3, 6, "*open*")
If IsArray(arrSum) Then
For i = 1 To UBound(arrSum)
Debug.Print arrSum(i, 1), arrSum(i, 2)
Next
End If
End Sub
Function SumBrand(lngColBrand As Long, _
lngColStatus As Long, _
lngColSales As Long, _
strStatus As String)
Dim rngBrand As Range
Dim iOffSetSTATUS As Integer, iOffSetSALES As Integer, i As Integer
Dim rngC As Range, objSum As Object, arrSum(), arrKeys
Set rngBrand = Range(Cells(2, lngColBrand), Cells(Rows.Count, lngColBrand).End(xlUp))
iOffSetSTATUS = lngColStatus - lngColBrand 'Versatz Statusspalte *open*
iOffSetSALES = lngColSales - lngColBrand 'Versatz Umsatzspalte
Set objSum = CreateObject("Scripting.Dictionary")
For Each rngC In rngBrand
If LCase(rngC.Offset(, iOffSetSTATUS)) Like LCase(strStatus) Then
objSum(rngC.Value) = objSum(rngC.Value) + rngC.Offset(, iOffSetSALES) * 1
End If
Next
If objSum.Count > 0 Then
arrKeys = objSum.keys
ReDim arrSum(1 To objSum.Count, 1 To 2)
For i = 0 To UBound(arrKeys)
arrSum(i + 1, 1) = arrKeys(i)
arrSum(i + 1, 2) = objSum(arrKeys(i))
Next
SumBrand = arrSum
End If
End Function
Gruß
Rudi