Sub machMittelwert()
Dim lBank As Long, lEmpl As Long
Dim colBank As Long
Dim dWert As Double
Dim lAnzahl As Long
colBank = 1
dWert = 0
lAnzahl = 0
For lBank = 1 To 10000
If InStr(LCase(Cells(lBank, colBank)), "bank1") > 0 Then
For lEmpl = lBank + 1 To lBank + 30
If InStr(LCase(Cells(lEmpl, colBank)), "bank1") > 0 Then Exit For
If InStr(LCase(Cells(lEmpl, colBank + 1)), "employment") > 0 Then
dWert = dWert + Cells(lEmpl, colBank + 4)
lAnzahl = lAnzahl + 1
Exit For
End If
Next
End If
Next
MsgBox "MIttelwert Bank1 = " & dWert / lAnzahl
End Sub