Sub Materialien()
Dim Best1 As Range
Dim Best2 As Range
Dim SpBez As String
Dim SpMat As String
Dim MatNr As String
Dim Summ As String
Sheets("Rohdaten").Select
Set Best1 = ActiveSheet.Range("A1:Z1").Find("Bezeichnung")
SpBez = Chr(Best1.Column + 64)
Set Best2 = ActiveSheet.Range("A1:Z1").Find("Material")
SpMat = Chr(Best2.Column + 64)
If Best1 Is Nothing Then
MsgBox ("Keine gewünschte Spalte gefunden")
Else
For i = 3 To 7
If InStr(1, ActiveSheet.Range(SpBez & i).Value, "Forecast") > 0 Then
MatNr = ActiveSheet.Range(SpMat & i).Value
Sheets("Preisliste").Select
Summ = Application.WorksheetFunction.VLookup(MatNr, Preisliste.Range("B:E"), 2, False)
MsgBox (Summ)
End If
Next
End If
End Sub