TxMarine1014
Technical User
am running into another problem where I have two loops in my file, but for some reason at the bottom of the 2nd loop it jumps to the very beginning of the file, and not the beginning of the 2nd loop.
Sub MacroExceptionTotal()
'
' MacroExceptionTotal Macro
' This Macro will add the totals by Exception Message Number as well as by their respective MRP area. This Macro will add all the Material that is Not on BOM.
'
'
'
n = 1
Windows("Monthly Exception message report.xlsx").Activate
Do While Cells(2, n) <> Empty
If Cells(3, n) <> Empty Then
n = n + 1
End If
Loop
Windows("LMP Exception Report.xlsm").Activate
Sheets("Exception Message Numbers").Select
ctr = 1
mtot = 0
x = n
y = 3
Z = 3
n = 1
XMSG = ActiveSheet.Cells(n, 5).Value 'saves value as Exception Message Number Value
MRPA = ActiveSheet.Cells(n, 8).Value 'saves value as MRP Area Value
Do While Cells(n, 1) <> Empty
TXMSG = ActiveSheet.Cells(n, 5).Value 'saves value as planned cost
TMRPA = ActiveSheet.Cells(n, 8).Value 'saves value as planned cost
TOT = ActiveSheet.Cells(n, 7).Value 'saves value as Counter
If TXMSG = XMSG Then
If TMRPA = MRPA Then
mtot = mtot + TOT
Else:
Windows("Monthly Exception Message Report.xlsx").Activate
Select Case MRPA
Case "3301"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
If TMRPA = "3301/SAREP" Then
y = y + 1
Else
If TMRPA = "3301/SRVIS" Then
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
Else
If TMRPA = "3301/UNSIS" Then
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
End If
End If
End If
Windows("LMP Exception Report.xlsm").Activate
Case "3301/SAREP"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
If TMRPA = "3301/SRVIS" Then
y = y + 1
Else
If TMRPA = "3301/UNSIS" Then
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
End If
End If
Windows("LMP Exception Report.xlsm").Activate
Case "3301/SERVIS"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
y = y + 1
Windows("LMP Exception Report.xlsm").Activate
End Select
MRPA = TMRPA
End If
Else
XMSG = TXMSG
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
y = 3
Z = Z + 1
Windows("LMP Exception Report.xlsm").Activate
End If
n = n + 1
Loop
Sub MacroExceptionTotal()
'
' MacroExceptionTotal Macro
' This Macro will add the totals by Exception Message Number as well as by their respective MRP area. This Macro will add all the Material that is Not on BOM.
'
'
'
n = 1
Windows("Monthly Exception message report.xlsx").Activate
Do While Cells(2, n) <> Empty
If Cells(3, n) <> Empty Then
n = n + 1
End If
Loop
Windows("LMP Exception Report.xlsm").Activate
Sheets("Exception Message Numbers").Select
ctr = 1
mtot = 0
x = n
y = 3
Z = 3
n = 1
XMSG = ActiveSheet.Cells(n, 5).Value 'saves value as Exception Message Number Value
MRPA = ActiveSheet.Cells(n, 8).Value 'saves value as MRP Area Value
Do While Cells(n, 1) <> Empty
TXMSG = ActiveSheet.Cells(n, 5).Value 'saves value as planned cost
TMRPA = ActiveSheet.Cells(n, 8).Value 'saves value as planned cost
TOT = ActiveSheet.Cells(n, 7).Value 'saves value as Counter
If TXMSG = XMSG Then
If TMRPA = MRPA Then
mtot = mtot + TOT
Else:
Windows("Monthly Exception Message Report.xlsx").Activate
Select Case MRPA
Case "3301"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
If TMRPA = "3301/SAREP" Then
y = y + 1
Else
If TMRPA = "3301/SRVIS" Then
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
Else
If TMRPA = "3301/UNSIS" Then
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
End If
End If
End If
Windows("LMP Exception Report.xlsm").Activate
Case "3301/SAREP"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
If TMRPA = "3301/SRVIS" Then
y = y + 1
Else
If TMRPA = "3301/UNSIS" Then
y = y + 1
ActiveSheet.Cells(y, x) = 0
y = y + 1
End If
End If
Windows("LMP Exception Report.xlsm").Activate
Case "3301/SERVIS"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
y = y + 1
Windows("LMP Exception Report.xlsm").Activate
End Select
MRPA = TMRPA
End If
Else
XMSG = TXMSG
Sheet = Sheets(Z).Select
ActiveSheet.Cells(y, x) = mtot
mtot = TOT
y = 3
Z = Z + 1
Windows("LMP Exception Report.xlsm").Activate
End If
n = n + 1
Loop