Problem with Running Two Loop statements within the same Macro

[TxMarine1014]

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

 

[MarkWalsh]

Hard for me to tell, but are you using the same variable as a counter for both loops?

 

[TxMarine1014]

Yes but I was under the impression after the first loop that I am reintializing it. Is this not the case? I included the portion of the Macro that was intended to do so below.



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

 

[MarkWalsh]

It's still hard for me to understand what you are trying to do; so I guess it might be possible to reusethe variable in your inner and outer loop in certain instances, but even if it were, I would never do this myself - I'd always use a separate variable for the inner and outer loops.

When the inner loop finishes, how does the outer loop know what the value of N was when it entered the inner loop?

 

[TxMarine1014]

I guess let me try to clarify what the problem really is. Both loops are seperate statements. However, when the second loop runs through to the bottom instead of returning to the beginning of the second loop it returns to the beginning of the program. The value of "n" seems to carry over fine at first as the first time through the loop outputs properly.

 

[mintjulep]

   Do While Cells(2, n) <> Empty
      
  
    If Cells(3, n) <> Empty Then
     n = n + 1
    End If
   
   Loop

And what happens when Cells(3, n) = Empty?

N doesn't get incremented and you keep evaluating the same cell forever.

 

[jges]

Do you have any code in the worksheet's select or activate events?

 

[TxMarine1014]

Mintjulep....it will always have data in it.


Jges....The Macro resides in the "LMP Exception Report.xlsm" report

 

[SkipVought]

Jges....The Macro resides in the "LMP Exception Report.xlsm" report

That does not answer his question.

You must look in the SHEET OBJECT CODE WINDOW (right-click the sheet tab of EACH SHEET that you are assigning values to, and select View Code)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TxMarine1014]

There was no code there when I used that opition

 

[SkipVought]

In the VB Editor, I would also open each Excel Object: the ThisWorkbook object and each Worksheet Object that you see in the Project Explorer Window to inspect the Object Code Window for any other code in the workbook that is not in a module, class module or userform.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I would set some break points, step thru the code, and see where the problem is....

Have fun.

---- Andy

 

[jges]

This may not be the source of your error, but I have noticed what appears to be an inconsistency in your code. If TXMSG = XMSG and TMRPA <> MRPA (2nd loop, first if statment = true and 2nd if statment = false) you activate a different window and select a certain sheet, but if TXMSG <> XMSG (2nd loop: first if statement evaluates false), you select a different sheet without activating the other window. Seems like you would want to activate the other window first or at least change the sheet back to "Exception Message Numbers" before you continue the loop.

That said, I agree with Andrzejek, stepping through the code with breakpoints and use of the debug window will be a good way to track down your bug(s).