Private Sub Worksheet_Change(ByVal Target As Range)
' Thomas Schilling Juli 2016
Dim rezeptedaten As Range
Dim suche As Range
Dim quelle As Worksheet
Dim ziel As Worksheet
Dim z As Integer
Dim x As String
Dim zeiger As String
z = 3
zeiger = "C" & z
Set quelle = Worksheets("Tabelle2")
Set ziel = Worksheets("Tabelle1")

If ziel.Range("A9").Value = "" Then
    GoTo Ende
End If
    

If Target.Address = "$A$9" Then

    ziel.Range("C3:I32").ClearContents
    
    ziel.Range("A9").Select
    
    For Each suche In quelle.Range("D2:D100")
        
        If suche.Value = Target Then
            x = suche.Row
            
            ziel.Range(zeiger).Value = quelle.Range("E" & x).Value
            zeiger = "D" & z
            ziel.Range(zeiger).FormulaLocal = "=E" & z & "+(E" & z & "*SVERWEIS(C" & z & ";Tabelle2!H2:I100;2;FALSCH))"
            zeiger = "E" & z
            ziel.Range(zeiger).Value = quelle.Range("F" & x).Value
            zeiger = "F" & z
            ziel.Range(zeiger).FormulaLocal = "=D" & z & "*$A$13*$A$21"
            zeiger = "G" & z
            ziel.Range(zeiger).FormulaLocal = "=D" & z & "*$A$15*$A$23"
            zeiger = "H" & z
            ziel.Range(zeiger).FormulaLocal = "=D" & z & "*$A$17*$A$25"
            zeiger = "I" & z
            ziel.Range(zeiger).FormulaLocal = "=SUMME(F" & z & ":H" & z & ")"
            
            z = z + 1
            zeiger = "C" & z
        End If
        
    
    Next suche
    
End If
Ende:


End Sub