Private
Sub
<strong>ProcessDataWithID</strong>(sWorksheet
As
String
, lStartRow
As
Long
, lEndRow
As
Long
, lColumn
As
Long
, iProcessID
As
Integer
, iNoDuplicates
As
Integer
)
Dim
lRow
As
Long
Dim
sActYearWeek
As
String
Dim
sNextYearWeek
As
String
Dim
sWeek
As
String
Dim
bCopy
As
Boolean
Dim
lNoDuplicatesRowActWeek
As
Long
Dim
lNoDuplicatesRowNextWeek
As
Long
sWeek = Right(Str(iActWeek), 2)
If
Left(sWeek, 1) =
" "
Then
sWeek =
"0"
+ Right(sWeek, 1)
End
If
sActYearWeek = Right(Val(iActYear), 4) & sWeek
sWeek = Right(Str(iNextWeek), 2)
If
Left(sWeek, 1) =
" "
Then
sWeek =
"0"
+ Right(sWeek, 1)
End
If
sNextYearWeek = Right(Val(iNextYear), 4) & sWeek
If
iNoDuplicates =
True
Then
lNoDuplicatesRowActWeek = 3
lNoDuplicatesRowNextWeek = 3
End
If
If
iNoDuplicates = iProcessID
Then
lNoDuplicatesRowActWeek = lDestRowActWeek
lNoDuplicatesRowNextWeek = lDestRowNextWeek
End
If
lRow = lStartRow
Do
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 3).Value <>
""
Or
_
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 5).Value <>
""
Or
_
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value <>
""
Or
_
(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 8).Value <> 0
And
_
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 8).Value <>
""
)
Or
_
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 10).Value <> 0
Then
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 1).Value = sActYearWeek
Then
If
GetProcessID(lRow) = iProcessID
Then
bCopy =
True
If
iNoDuplicates = iProcessID
Or
iNoDuplicates =
True
Then
If
CheckDuplicateEntry(sWorksheet, lNoDuplicatesRowActWeek, 1, lRow) =
True
Then
bCopy =
False
End
If
End
If
If
sWorksheet = sWorksheet_Serviceold
Then
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value =
"Service generell"
And
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 13).Value =
""
Then
bCopy =
False
End
If
End
If
If
bCopy =
True
Then
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 1).Interior.ColorIndex = 36
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 1).Interior.Pattern = xlSolid
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 1).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 2).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 2).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 3).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 3).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 4).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 4).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 5).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 5).Value = Mid(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value, 1, 2) &
" "
& Mid(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value, 3, 2) &
" "
& Mid(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value, 5)
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 6).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 8).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 7).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 10).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 8).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 11).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 9).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 12).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 10).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 13).Value
If
iNoDuplicates =
False
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 10).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value
End
If
If
sWorksheet = sWorksheet_Serviceold
Then
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value <>
"Service generell"
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 10).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value
End
If
End
If
If
sWorksheet = sWorksheet_Konfektion
Then
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"R01"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 11).Value =
"Gummi vorhanden"
End
If
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"R02"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 11).Value =
"Wird in Mischerei gefertigt"
End
If
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"R03"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 11).Value =
"Gummi ist bestellt"
End
If
End
If
If
sWorksheet = sWorksheet_PUuCC
Then
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"C01"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 11).Value =
"Harz-Mischung vorhanden"
End
If
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"C02"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, 11).Value =
"Wird in CC-Mischerei gefertigt"
End
If
End
If
lDestRowActWeek = lDestRowActWeek + 1
End
If
End
If
End
If
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 1).Value = sNextYearWeek
Then
If
GetProcessID(lRow) = iProcessID
Then
bCopy =
True
If
iNoDuplicates = iProcessID
Or
iNoDuplicates =
True
Then
If
CheckDuplicateEntry(sWorksheet, lNoDuplicatesRowNextWeek, lColumn, lRow) =
True
Then
bCopy =
False
End
If
End
If
If
sWorksheet = sWorksheet_Serviceold
Then
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value =
"Service generell"
And
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, lColumn + 2).Value =
""
Then
bCopy =
False
End
If
End
If
If
bCopy =
True
Then
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 1).Interior.ColorIndex = 40
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 1).Interior.Pattern = xlSolid
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 2).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 1).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 3).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 2).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 4).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 3).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 5).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 4).Value = Mid(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value, 1, 2) &
" "
& Mid(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value, 3, 2) &
" "
& Mid(oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 7).Value, 5)
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 5).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 8).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 6).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 10).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 7).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 11).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 8).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 12).Value
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 9).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 13).Value
If
iNoDuplicates =
False
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 9).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value
End
If
If
sWorksheet = sWorksheet_Serviceold
Then
If
oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value <>
"Service generell"
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowNextWeek, lColumn + 9).Value = oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 6).Value
End
If
End
If
If
sWorksheet = sWorksheet_Konfektion
Then
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"R01"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, lColumn + 10).Value =
"Gummi vorhanden"
End
If
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"R02"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, lColumn + 10).Value =
"Wird in Mischerei gefertigt"
End
If
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"R03"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, lColumn + 10).Value =
"Gummi ist bestellt"
End
If
End
If
If
sWorksheet = sWorksheet_PUuCC
Then
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"C01"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, lColumn + 10).Value =
"Harz-Mischung vorhanden"
End
If
If
InStr(1, oWorkbook.Worksheets(sWorksheet_Exportdaten).Cells(lRow, 16).Value,
"C02"
)
Then
oWorkbook.Worksheets(sWorksheet).Cells(lDestRowActWeek, lColumn + 10).Value =
"Wird in CC-Mischerei gefertigt"
End
If
End
If
lDestRowNextWeek = lDestRowNextWeek + 1
End
If
End
If
End
If
End
If
If
lRow > lEndRow
Then
Exit
Do
End
If
lRow = lRow + 1
Loop
End
Sub