Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Case without Select Case error problem
Can someone take a look at this code and tell me why I am getting a "Case
without Select Case" error on "Case 4" ? Thank you. Private Sub FillSummary(cs, rw, lastrow) ' 'Macro to Populate the Project Summary Report 'Macro written 5/13/2008 by Ayoade Ojikutu ' Dim c As Range, c1 As Range, clusterRange As Range, commentsRange As Range, ms155Range As Range Dim i As Integer, charPos As Integer, colIndex As Integer Dim searchString As String, searchChar As String Select Case cs Case 1 Worksheets(2).Range("B2:M8").Select Application.CutCopyMode = False Selection.Copy Worksheets(1).Activate Range("B6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Case 2 searchChar = "/" colIndex = 17 For Each c In Worksheets(2).Range("B12:B15").Cells searchString = c.Value charPos = InStr(1, searchString, searchChar, 1) Worksheets(1).Range("B" & colIndex).Value = Mid(c.Value, charPos + 2, 3) Worksheets(1).Range("C" & colIndex).Value = Mid(c.Value, 1, charPos - 2) Worksheets(1).Range("D" & colIndex).Value = Worksheets(1).Range("B" & colIndex).Value - Worksheets(1).Range("C" & colIndex).Value Worksheets(1).Range("E" & colIndex).Value = c.Offset(0, 1).Value Worksheets(1).Range("F" & colIndex).Value = Worksheets(1).Range("C" & colIndex).Value / Worksheets(1).Range("B" & colIndex).Value colIndex = colIndex + 1 Next c Worksheets(1).Select With Selection .Range("B22").Value = Application.WorksheetFunction.Sum(Range("B17:B20") ) .Range("C22").Value = Application.WorksheetFunction.Sum(Range("C17:C20") ) .Range("D22").Value = Application.WorksheetFunction.Sum(Range("D17:D20") ) .Range("F22").Value = Application.WorksheetFunction.Sum(Range("F17:F20") ) End With Case 3 Set clusterRange = Worksheets(2).Range("B6:B" & lastrow) Set commentsRange = Worksheets(2).Range("AF6:D" & lastrow) Set ms155Range = Worksheets(2).Range("AO6:AO" & lastrow) For Each c1 In Worksheets(1).Range("A27:A32").Cells c1.Select i = 0 For Each c In clusterRange.Cells If Mid(c.Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If Next c c1.Offset(0, 1).Value = i i = 0 For Each c In commentsRange.Cells If c.Value = "Accepted" And c.Offset(0, 9).Value = "Yes" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 2).Value = i i = 0 For Each c In ms155Range.Cells If c.Value = "Not completed" And c.Offset(0, 9).Value = "N/A" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 3).Value = i i = 0 For Each c In ms155Range.Cells If c.Value = "Nothing Received" And c.Offset(0, 9).Value = "N/A" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 4).Value = i Case 4 Set clusterRange = Worksheets(2).Range("D3:D" & lastrow) Set ms095Range = Worksheets(2).Range("O3:O" & lastrow) Set ms109Range = Worksheets(2).Range("U3:U" & lastrow) Set launchRange = Worksheets(2).Range("AM3:AM" & lastrow) For Each c1 In Worksheets(1).Range("A39:A46").Cells c1.Select i = 0 For Each c In clusterRange.Cells If c.Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 1).Value = i i = 0 For Each c In ms095Range.Cells If c.Value < "" And c.Offset(0, -11).Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 2).Value = i i = 0 For Each c In ms109Range.Cells If c.Value < "" And c.Offset(0, -17).Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 3).Value = i Dim launchDate As Date i = 0 For Each c In launchRange.Cells If c.Value < "" And c.Offset(0, -35).Value = Right(c1.Value, 1) Then launchDate = c.Value End If Next c c1.Offset(0, 4).Value = launchDate Next c1 End Select End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Case without Select Case error problem
Hi,
It's because there's no Next c1 fot this loop. You must close the loop before going onto Case 4 Case 3 Set clusterRange = Worksheets(2).Range("B6:B" & lastrow) Set commentsRange = Worksheets(2).Range("AF6:D" & lastrow) Set ms155Range = Worksheets(2).Range("AO6:AO" & lastrow) For Each c1 In Worksheets(1).Range("A27:A32").Cells Mike "Ayo" wrote: Can someone take a look at this code and tell me why I am getting a "Case without Select Case" error on "Case 4" ? Thank you. Private Sub FillSummary(cs, rw, lastrow) ' 'Macro to Populate the Project Summary Report 'Macro written 5/13/2008 by Ayoade Ojikutu ' Dim c As Range, c1 As Range, clusterRange As Range, commentsRange As Range, ms155Range As Range Dim i As Integer, charPos As Integer, colIndex As Integer Dim searchString As String, searchChar As String Select Case cs Case 1 Worksheets(2).Range("B2:M8").Select Application.CutCopyMode = False Selection.Copy Worksheets(1).Activate Range("B6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Case 2 searchChar = "/" colIndex = 17 For Each c In Worksheets(2).Range("B12:B15").Cells searchString = c.Value charPos = InStr(1, searchString, searchChar, 1) Worksheets(1).Range("B" & colIndex).Value = Mid(c.Value, charPos + 2, 3) Worksheets(1).Range("C" & colIndex).Value = Mid(c.Value, 1, charPos - 2) Worksheets(1).Range("D" & colIndex).Value = Worksheets(1).Range("B" & colIndex).Value - Worksheets(1).Range("C" & colIndex).Value Worksheets(1).Range("E" & colIndex).Value = c.Offset(0, 1).Value Worksheets(1).Range("F" & colIndex).Value = Worksheets(1).Range("C" & colIndex).Value / Worksheets(1).Range("B" & colIndex).Value colIndex = colIndex + 1 Next c Worksheets(1).Select With Selection .Range("B22").Value = Application.WorksheetFunction.Sum(Range("B17:B20") ) .Range("C22").Value = Application.WorksheetFunction.Sum(Range("C17:C20") ) .Range("D22").Value = Application.WorksheetFunction.Sum(Range("D17:D20") ) .Range("F22").Value = Application.WorksheetFunction.Sum(Range("F17:F20") ) End With Case 3 Set clusterRange = Worksheets(2).Range("B6:B" & lastrow) Set commentsRange = Worksheets(2).Range("AF6:D" & lastrow) Set ms155Range = Worksheets(2).Range("AO6:AO" & lastrow) For Each c1 In Worksheets(1).Range("A27:A32").Cells c1.Select i = 0 For Each c In clusterRange.Cells If Mid(c.Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If Next c c1.Offset(0, 1).Value = i i = 0 For Each c In commentsRange.Cells If c.Value = "Accepted" And c.Offset(0, 9).Value = "Yes" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 2).Value = i i = 0 For Each c In ms155Range.Cells If c.Value = "Not completed" And c.Offset(0, 9).Value = "N/A" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 3).Value = i i = 0 For Each c In ms155Range.Cells If c.Value = "Nothing Received" And c.Offset(0, 9).Value = "N/A" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 4).Value = i Case 4 Set clusterRange = Worksheets(2).Range("D3:D" & lastrow) Set ms095Range = Worksheets(2).Range("O3:O" & lastrow) Set ms109Range = Worksheets(2).Range("U3:U" & lastrow) Set launchRange = Worksheets(2).Range("AM3:AM" & lastrow) For Each c1 In Worksheets(1).Range("A39:A46").Cells c1.Select i = 0 For Each c In clusterRange.Cells If c.Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 1).Value = i i = 0 For Each c In ms095Range.Cells If c.Value < "" And c.Offset(0, -11).Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 2).Value = i i = 0 For Each c In ms109Range.Cells If c.Value < "" And c.Offset(0, -17).Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 3).Value = i Dim launchDate As Date i = 0 For Each c In launchRange.Cells If c.Value < "" And c.Offset(0, -35).Value = Right(c1.Value, 1) Then launchDate = c.Value End If Next c c1.Offset(0, 4).Value = launchDate Next c1 End Select End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Case without Select Case error problem
Thanks. I found it.
"Mike H" wrote: Hi, It's because there's no Next c1 fot this loop. You must close the loop before going onto Case 4 Case 3 Set clusterRange = Worksheets(2).Range("B6:B" & lastrow) Set commentsRange = Worksheets(2).Range("AF6:D" & lastrow) Set ms155Range = Worksheets(2).Range("AO6:AO" & lastrow) For Each c1 In Worksheets(1).Range("A27:A32").Cells Mike "Ayo" wrote: Can someone take a look at this code and tell me why I am getting a "Case without Select Case" error on "Case 4" ? Thank you. Private Sub FillSummary(cs, rw, lastrow) ' 'Macro to Populate the Project Summary Report 'Macro written 5/13/2008 by Ayoade Ojikutu ' Dim c As Range, c1 As Range, clusterRange As Range, commentsRange As Range, ms155Range As Range Dim i As Integer, charPos As Integer, colIndex As Integer Dim searchString As String, searchChar As String Select Case cs Case 1 Worksheets(2).Range("B2:M8").Select Application.CutCopyMode = False Selection.Copy Worksheets(1).Activate Range("B6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Case 2 searchChar = "/" colIndex = 17 For Each c In Worksheets(2).Range("B12:B15").Cells searchString = c.Value charPos = InStr(1, searchString, searchChar, 1) Worksheets(1).Range("B" & colIndex).Value = Mid(c.Value, charPos + 2, 3) Worksheets(1).Range("C" & colIndex).Value = Mid(c.Value, 1, charPos - 2) Worksheets(1).Range("D" & colIndex).Value = Worksheets(1).Range("B" & colIndex).Value - Worksheets(1).Range("C" & colIndex).Value Worksheets(1).Range("E" & colIndex).Value = c.Offset(0, 1).Value Worksheets(1).Range("F" & colIndex).Value = Worksheets(1).Range("C" & colIndex).Value / Worksheets(1).Range("B" & colIndex).Value colIndex = colIndex + 1 Next c Worksheets(1).Select With Selection .Range("B22").Value = Application.WorksheetFunction.Sum(Range("B17:B20") ) .Range("C22").Value = Application.WorksheetFunction.Sum(Range("C17:C20") ) .Range("D22").Value = Application.WorksheetFunction.Sum(Range("D17:D20") ) .Range("F22").Value = Application.WorksheetFunction.Sum(Range("F17:F20") ) End With Case 3 Set clusterRange = Worksheets(2).Range("B6:B" & lastrow) Set commentsRange = Worksheets(2).Range("AF6:D" & lastrow) Set ms155Range = Worksheets(2).Range("AO6:AO" & lastrow) For Each c1 In Worksheets(1).Range("A27:A32").Cells c1.Select i = 0 For Each c In clusterRange.Cells If Mid(c.Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If Next c c1.Offset(0, 1).Value = i i = 0 For Each c In commentsRange.Cells If c.Value = "Accepted" And c.Offset(0, 9).Value = "Yes" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 2).Value = i i = 0 For Each c In ms155Range.Cells If c.Value = "Not completed" And c.Offset(0, 9).Value = "N/A" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 3).Value = i i = 0 For Each c In ms155Range.Cells If c.Value = "Nothing Received" And c.Offset(0, 9).Value = "N/A" Then If Mid(c.Offset(0, -9).Value, 4, 1) = Right(c1.Value, 1) Or Mid(c.Offset(0, -9).Value, 3, 2) = Right(c1.Value, 2) Then i = i + 1 End If End If Next c c1.Offset(0, 4).Value = i Case 4 Set clusterRange = Worksheets(2).Range("D3:D" & lastrow) Set ms095Range = Worksheets(2).Range("O3:O" & lastrow) Set ms109Range = Worksheets(2).Range("U3:U" & lastrow) Set launchRange = Worksheets(2).Range("AM3:AM" & lastrow) For Each c1 In Worksheets(1).Range("A39:A46").Cells c1.Select i = 0 For Each c In clusterRange.Cells If c.Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 1).Value = i i = 0 For Each c In ms095Range.Cells If c.Value < "" And c.Offset(0, -11).Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 2).Value = i i = 0 For Each c In ms109Range.Cells If c.Value < "" And c.Offset(0, -17).Value = Right(c1.Value, 1) Then i = i + 1 End If Next c c1.Offset(0, 3).Value = i Dim launchDate As Date i = 0 For Each c In launchRange.Cells If c.Value < "" And c.Offset(0, -35).Value = Right(c1.Value, 1) Then launchDate = c.Value End If Next c c1.Offset(0, 4).Value = launchDate Next c1 End Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change from upper case to proper case in excel 2002 | Excel Discussion (Misc queries) | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How to use formula auditing to change upper case to Title Case. | Excel Worksheet Functions |