VBA Loop Case..If syntax

O

okelly

Hi

I'm getting *-"Next without for"-* error on line "*Next i*". Th
related -*for*- statement is *-For i = 3To FinalRow-*
but I must have closed the loop someplace in the syntax.

The macro does the following
1. For IPU servers writes a tag of "N/A"
2. Reviews "CurrentClass" and assigns values of "4YOS" or "N/A"
3. For the remaining servers not "N/A" or "4YOS"..
5. If mfg date >01/06/2004 _AND_ install date > 01/10/2004 assign valu
of "new" otherwise assign all unremaining untagged servers as "old"

Can somebody indicate:
(a)..correct syntax for the "For"..."Next i" loop.
(b) is syntax for (5) above correct?

Thanks in advance



Code
-------------------


Sub ServerCategory()
Sheets("Sheet1").Select
FinalRow = Cells(65536, 1).End(xlUp).Row
For i = 3 To FinalRow
CommServerMap = Cells(i, 7).Value
CurrentClass = Cells(i, 29).Value
MfgDate = Cells(i, 34).Value
InstallDate = Cells(i, 35).Value

Select Case CommServerMap
Case "IPU Server"
IPU = True
Case Else
IPU = False
End Select

If IPU Then
Cells(i, 32).Value = "N/A"
Else
Select Case CurrentClass
Case "A1s", "A2s", "A3s", "HOA1s", "HOA2s", "HOA3s", "SOA1s", "SOA2s", "SOA3s", "SOI1s", "SOI2s", "SOI3s"
Cells(i, 32).Value = "4YOS"

Case "Not in HP scope", "Out of production", "Pending", "NBA1", "NBA2", "NBI1", "NBI2", "I1", "I2", "I3"
Cells(i, 32).Value = "N/A"

End Select ' ends select current class

Select Case MfgDate
Case Is > 38139
If InstallDate > 38261 Then
Cells(i, 32).Value = "New"
Else
Cells(i, 32).Value = "Old"

End If 'is server an IPU server

Next i

MsgBox "Classification have been applied"

End Sub
 
D

Dave Patrick

Missing the 'End If' for 'If IPU Then'


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|
| Hi
|
| I'm getting *-"Next without for"-* error on line "*Next i*". The
| related -*for*- statement is *-For i = 3To FinalRow-*
| but I must have closed the loop someplace in the syntax.
|
| The macro does the following
| 1. For IPU servers writes a tag of "N/A"
| 2. Reviews "CurrentClass" and assigns values of "4YOS" or "N/A"
| 3. For the remaining servers not "N/A" or "4YOS"..
| 5. If mfg date >01/06/2004 _AND_ install date > 01/10/2004 assign value
| of "new" otherwise assign all unremaining untagged servers as "old"
|
| Can somebody indicate:
| (a)..correct syntax for the "For"..."Next i" loop.
| (b) is syntax for (5) above correct?
|
| Thanks in advance
|
|
|
| Code:
| --------------------
|
|
| Sub ServerCategory()
| Sheets("Sheet1").Select
| FinalRow = Cells(65536, 1).End(xlUp).Row
| For i = 3 To FinalRow
| CommServerMap = Cells(i, 7).Value
| CurrentClass = Cells(i, 29).Value
| MfgDate = Cells(i, 34).Value
| InstallDate = Cells(i, 35).Value
|
| Select Case CommServerMap
| Case "IPU Server"
| IPU = True
| Case Else
| IPU = False
| End Select
|
| If IPU Then
| Cells(i, 32).Value = "N/A"
| Else
| Select Case CurrentClass
| Case "A1s", "A2s", "A3s", "HOA1s", "HOA2s", "HOA3s", "SOA1s", "SOA2s",
"SOA3s", "SOI1s", "SOI2s", "SOI3s"
| Cells(i, 32).Value = "4YOS"
|
| Case "Not in HP scope", "Out of production", "Pending", "NBA1", "NBA2",
"NBI1", "NBI2", "I1", "I2", "I3"
| Cells(i, 32).Value = "N/A"
|
| End Select ' ends select current class
|
| Select Case MfgDate
| Case Is > 38139
| If InstallDate > 38261 Then
| Cells(i, 32).Value = "New"
| Else
| Cells(i, 32).Value = "Old"
|
| End If 'is server an IPU server
|
| Next i
|
| MsgBox "Classification have been applied"
|
| End Sub
|
| --------------------
|
|
| --
| okelly
| ------------------------------------------------------------------------
| okelly's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36708
| View this thread: http://www.excelforum.com/showthread.php?threadid=568781
|
 
D

Dana DeLouis

For a debugging technique, try indenting. Here, I don't see an "End Select"

Select Case MfgDate
Case Is > 38139
If InstallDate > 38261 Then
Cells(i, 32).Value = "New"
Else
Cells(i, 32).Value = "Old"
End If 'is server an IPU server
Next i
 
O

okelly

Thanks folks. Got it working with the following


Code:
--------------------

Sub test3()
Sheets("Sheet1").Select
FinalRow = Cells(65536, 1).End(xlUp).Row
For i = 3 To FinalRow
CommServerMap = Cells(i, 7).Value
CurrentClass = Cells(i, 29).Value
MfgDate = Cells(i, 34).Value
InstallDate = Cells(i, 35).Value

Select Case CommServerMap
Case "IPU Server"
IPU = True
Case Else
IPU = False
End Select

If IPU Then
Cells(i, 32).Value = "N/A"
Else
Select Case CurrentClass
Case "A1s", "A2s", "A3s", "HOA1s", "HOA2s", "HOA3s", "SOA1s", "SOA2s", "SOA3s", "SOI1s", "SOI2s", "SOI3s"
Cells(i, 32).Value = "4YOS"

Case "Not in HP scope", "Out of production", "Pending", "NBA1", "NBA2", "NBI1", "NBI2", "I1", "I2", "I3"
Cells(i, 32).Value = "N/A"

Case "A1", "A2", "A3", "HOA1", "HOA2", "HOA3", "SOA1", "SOA2", "SOA3", "SOI1", "SOI2", "SOI3"
If MfgDate > 38139 Then
Cells(i, 32).Value = "New"
Else
Cells(i, 32).Value = "Old"
End If
End Select ' ends select current class

End If 'is server an IPU server
Next i

MsgBox "Classification have been applied"

End Sub
 
D

Dave Patrick

You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|
| Thanks folks. Got it working with the following
|
|
| Code:
| --------------------
|
| Sub test3()
| Sheets("Sheet1").Select
| FinalRow = Cells(65536, 1).End(xlUp).Row
| For i = 3 To FinalRow
| CommServerMap = Cells(i, 7).Value
| CurrentClass = Cells(i, 29).Value
| MfgDate = Cells(i, 34).Value
| InstallDate = Cells(i, 35).Value
|
| Select Case CommServerMap
| Case "IPU Server"
| IPU = True
| Case Else
| IPU = False
| End Select
|
| If IPU Then
| Cells(i, 32).Value = "N/A"
| Else
| Select Case CurrentClass
| Case "A1s", "A2s", "A3s", "HOA1s", "HOA2s", "HOA3s", "SOA1s", "SOA2s",
"SOA3s", "SOI1s", "SOI2s", "SOI3s"
| Cells(i, 32).Value = "4YOS"
|
| Case "Not in HP scope", "Out of production", "Pending", "NBA1", "NBA2",
"NBI1", "NBI2", "I1", "I2", "I3"
| Cells(i, 32).Value = "N/A"
|
| Case "A1", "A2", "A3", "HOA1", "HOA2", "HOA3", "SOA1", "SOA2", "SOA3",
"SOI1", "SOI2", "SOI3"
| If MfgDate > 38139 Then
| Cells(i, 32).Value = "New"
| Else
| Cells(i, 32).Value = "Old"
| End If
| End Select ' ends select current class
|
| End If 'is server an IPU server
| Next i
|
| MsgBox "Classification have been applied"
|
| End Sub
|
|
|
|
| --------------------
|
|
| --
| okelly
| ------------------------------------------------------------------------
| okelly's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36708
| View this thread: http://www.excelforum.com/showthread.php?threadid=568781
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop to copy paste rows base on table arrays 0
For, Next, Loop vs. Select / Case Is 6
Loop 1
Start Do/Loop within conditional statement 4
Select....Case macro in Excel 2007 3
Counting Criteria Values with For Loop 3
converting a for next loop into a for each loop 2
looping issue 10

Top