Hey everybody,

I have two sheets one of them with names in b31:b40 (sheetname:"ziel")
and anotherone with lots of data (sheet quelle).
I want the macro to take the value in b31 and look for alike values (values that contain the b31) in the sheet "quelle".

The quelle sheet is autofiltered and the macro is supposed to take the subtotal after filtering all alike values.
Unfortunally the way I try it doesn´t work.
Anyone an idea please?

Thanky you very much!

regards el_don










Sub variable()


    Sheets("ziel").Select
    Dim MyArr As Variant
    Dim i As Long
    Dim Rng As Range
    Set Rng = Range("b31:b40")
    MyArr = Rng.Value
    For i = LBound(MyArr, 1) To UBound(MyArr, 1)
    Sheets("quelle").Select
  Selection.AutoFilter Field:=16, Criteria1:=MyArr(i, 1)  'Here I think I need something like "=*myarr(i,1)*" but unfortunaly it doesn´t work
    Range("S874").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-866]C:R[-1]C)"
    Range("S874").Select
    Selection.Copy
    Sheets("ziel").Select
    Range("C31").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  ActiveSheet.Unprotect
  Next i


    End Sub