es ist zum heulen... Summenproduk funktioniert n.

Sub Start_Matching()
'Main sub, runs all macros
update_Start
Concatenate_Steps
Concatenate_Eval
Match_Concatenates
update_end
End Sub

Sub load_variables()
'loads the variables
Set oBKm = ActiveWorkbook
Set oSTmode = oBKm.Sheets("Mode")
Set oSTeval = oBKm.Sheets("eval")
End Sub

Sub update_Start()

'Initialize the performance-enhancement and calls load variables

Application.Calculation = xlCalculationManual

Application.DisplayAlerts = False

Application.ScreenUpdating = False

load_variables

End Sub

Sub update_end()

'ends performance-enhancement measures

Application.Calculation = xlCalculationAutomatic

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Sub Concatenate_Steps()
'concatenates cell value in column BA for each sheet as stated in the "Mode"-Sheet
i = 1
'Do for each entry in the mode sheet
Do Until [steps_start].Offset(i, 0) = ""
str1 = [steps_start].Offset(i, 0)
Set oSTstep = Sheets(str1)
With oSTstep
'clears old data in column BA
Range(.Cells(2, 53), .Cells(15000, 53)).ClearContents
ii = 2
'Concatenates
Do Until .Cells(ii, 1) = ""
.Cells(ii, 53) = .Cells(ii, 1) & "-" & str1 & "-" & .Cells(ii, 2) & "-" & .Cells(ii, _
_
6) & "-" & .Cells(ii, 4) & "-" & .Cells(ii, 5)
ii = ii + 1
Loop
End With
i = i + 1
Loop
End Sub

Sub Concatenate_Eval()

'concatenates cell value in column BA in the eval sheet

With oSTeval

'clears old data in column BA

Range(.Cells(3, 53), .Cells(15000, 53)).ClearContents

ii = 3

'concatenates

Do Until .Cells(ii, 1) = ""

.Cells(ii, 53) = .Cells(ii, 1) & "-" & .Cells(ii, 2) & "-" & .Cells(ii, 3) & "-" & . _

Cells(ii, 4) & "-" & .Cells(ii, 5) & "-" & .Cells(ii, 6)

ii = ii + 1

Loop

End With

End Sub

Sub Match_Concatenates()

'matches concatenated values in the BA column of Step-Sheet to eval sheet and enters values, if  _

available or "No Value" if entry not found

With oSTeval

'clears old date in eval sheet

Range(.Cells(3, 7), .Cells(15000, 47)).ClearContents

'gets max column

imaxcol = Application.Count(Rows(1)) + 6

End With

i = 3

'for each entry in eval-sheet (no empty rows in between in the eval sheet!)

Do Until oSTeval.Cells(i, 53) = ""

'define the concatenate string in column BA of the eval sheet to look for in the  _

step sheet

str1 = oSTeval.Cells(i, 53)

'define which step sheet to search

str2 = oSTeval.Cells(i, 2)

'safety check: if no value for the sheet name is in column 2 of the eval sheet,  _

enter "No Value"

If str2 = "" Then

For icol = 7 To imaxcol Step 1

oSTeval.Cells(i, icol) = 0

Next icol

Else

With Sheets(str2)

ii = 2

iOut = 0

'Look for the concetanate string in the step sheet, cycling through columns

Do Until .Cells(ii, 53) = str1

ii = ii + 1

If ii > 1000 Then

iOut = 1

Exit Do

End If

Loop

'safety stop at row 1000 in the eval sheet, if string not found, enter "No  _

Value"

If iOut = 1 Then

For icol = 7 To imaxcol Step 1

oSTeval.Cells(i, icol) = 0

Next icol

Else

'if string found, enter value

For icol = 7 To imaxcol Step 1

iMatch = oSTeval.Cells(1, icol)

oSTeval.Cells(i, icol) = .Cells(ii, iMatch)

Next icol

End If

End With

End If

i = i + 1

Loop

End Sub