VBA Case Error

[TxMarine1014]

I am getting an error of Case without Select Case and I do not know why. I have used code similar to this in some previous Macros, so I would assume that it would work unfortunately it did not. Just a little background on to what I am trying to do:

I have two Worksheets that I am working with, and I am trying to total up the information and write it on to my acutal report that is mentioned in the Macro as "Montly Exception Report.xlsx". Any help would be great!


Do While Cells(n, 1) <> Empty




TXMSG = ActiveSheet.Cells(n, 5).Value 'saves value as plannned 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(x, y) = mtot
mtot = TOT
If TMRPA = "3301/SAREP" Then
y = y + 1
Else
If TMRPA = "3301/SRVIS" Then
y = y + 1
ActiveSheet.Cells(x, y) = 0
y = y + 1
Else
If TMRPA = "3301/UNSIS" Then
y = y + 1
ActiveSheet.Cells(x, y) = 0
y = y + 1
ActiveSheet.Cells(x, y) = 0
y = y + 1
End If
Windows("LMP Exception Report.xlsm").Activate
Case "3301/SAREP"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(x, y) = mtot
mtot = TOT
If TMRPA = "3301/SRVIS" Then
y = y + 1
Else
If TMRPA = "3301/UNSIS" Then
y = y + 1
ActiveSheet.Cells(x, y) = 0
y = y + 1
End If
Windows("LMP Exception Report.xlsm").Activate
Case "3301/SERVIS"
Sheet = Sheets(Z).Select
ActiveSheet.Cells(x, y) = mtot
mtot = TOT
y = y + 1
Windows("LMP Exception Report.xlsm").Activate
End If
End Select
Else
Sheet = Sheets(Z).Select
ActiveSheet.Cells(x, y) = mtot
mtot = TOT
y = 3
Z = Z + 1
Windows("LMP Exception Report.xlsm").Activate
End If


n = n + 1

Loop

 

[MakeItSo]

Oh my!


That is because you seem to not properly understand the use of "if then else" as well as the need for an END IF.

One thing I find extremely useful when programming is to use indentation. This way one can always check/verify the level of hierarchy you're currently on.

I've indented your code to reflect the hierarchy levels. You have multiple IFs without END IF.
See for yourself in your code, indented but unaltered else:

Do While Cells(n, 1) <> Empty

	TXMSG = ActiveSheet.Cells(n, 5).Value 'saves value as plannned 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(x, y) = mtot
					mtot = TOT
					If TMRPA = "3301/SAREP" Then
						y = y + 1
					Else
						If TMRPA = "3301/SRVIS" Then
							y = y + 1
							ActiveSheet.Cells(x, y) = 0
							y = y + 1
						Else
							If TMRPA = "3301/UNSIS" Then
								y = y + 1
								ActiveSheet.Cells(x, y) = 0
								y = y + 1
								ActiveSheet.Cells(x, y) = 0
								y = y + 1
							End If
							Windows("LMP Exception Report.xlsm").Activate

				Case "3301/SAREP"
					Sheet = Sheets(Z).Select
					ActiveSheet.Cells(x, y) = mtot
					mtot = TOT
					If TMRPA = "3301/SRVIS" Then
						y = y + 1
					Else
						If TMRPA = "3301/UNSIS" Then
							y = y + 1
							ActiveSheet.Cells(x, y) = 0
							y = y + 1
						End If
						Windows("LMP Exception Report.xlsm").Activate

						Case "3301/SERVIS"
							Sheet = Sheets(Z).Select
							ActiveSheet.Cells(x, y) = mtot
							mtot = TOT
							y = y + 1
							Windows("LMP Exception Report.xlsm").Activate
							End If
			End Select
		Else
			Sheet = Sheets(Z).Select
			ActiveSheet.Cells(x, y) = mtot
			mtot = TOT
			y = 3
			Z = Z + 1
			Windows("LMP Exception Report.xlsm").Activate
	End If

	n = n + 1

Loop

The hierarchy levels with missing END IF you should be able to see for yourself in the above indented notation.


Hope this helps.

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[TxMarine1014]

Okay that helped! Thank you very much, but now I 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






End Sub

 

[Andrzejek]

Have you tried to set some Breakpoints in your code and step thru it to see what's going on?

Place the cursor on the line of code where you want to stop, hit F9, run your code, when you stop on the line of code, hit F8 to step thru it. You can place your cursor over the variables to see the values in them.

Help yourself


Have fun.

---- Andy

 

[SkipVought]

BTW, this appears to be VBA in Excel. YES?

If so, you need to post in forum707.

I suspect that your use of Activate, Select and ActiveCell may be contributing to your problems.

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

 

[TxMarine1014]

Thank you I was not aware of this forum I will go ahead and post there