| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Loop Case..If syntax
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 ------------------- -- okell ----------------------------------------------------------------------- okelly's Profile: http://www.excelforum.com/member.php...fo&userid=3670 View this thread: http://www.excelforum.com/showthread.php?threadid=56878 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Loop Case..If syntax
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 "okelly" wrote: | | 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...o&userid=36708 | View this thread: http://www.excelforum.com/showthread...hreadid=568781 | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Loop Case..If syntax
Maybe change that to;
You're short one 'End If' -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Loop Case..If syntax
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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "okelly" wrote in message ... 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...o&userid=36708 View this thread: http://www.excelforum.com/showthread...hreadid=568781 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Loop Case..If syntax
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...o&userid=36708 View this thread: http://www.excelforum.com/showthread...hreadid=568781 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Loop Case..If syntax
You're welcome.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "okelly" wrote: | | 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...o&userid=36708 | View this thread: http://www.excelforum.com/showthread...hreadid=568781 | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| loop syntax | Excel Programming | |||
| syntax for to upper case letters? | Excel Programming | |||
| Select Case for Active Worksheet syntax. | Excel Programming | |||
| Syntax for Select Case | Excel Programming | |||
| Select Case syntax | Excel Programming | |||
![okelly[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
