Solved: Select Case problem

[expfresocspy]

I'm trying to group the schools according to the levels .
The code looks like this :

Dim rData2 As Range, rCell2 As Range

Set rData2 = Nothing
On Error Resume Next
Set rData2 = ActiveSheet.Columns("AD"). _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rData2 Is Nothing Then Exit Sub

For Each rCell2 In rData2.Cells
Select Case rCell2.Value
Case (school name starting with "Kindergarten of")
rCell2.Offset(0, 1).Value = "Kindergarten"
Case (school name starting with "College of")
rCell2.Offset(0, 1).Value = "College"
Case (school name starting with "University of")
rCell2.Offset(0, 1).Value = "University"
Case Else
rCell2.Offset(0, 1).Value = "Others"
End Select
Next


The highlighted in red font is the one I want to change to suit the condition. How do I go about doing it ?
Thanks in advance ! <3

[macropod]

Hi expfresocspy,

I think you'll need to provide a few more clues! There's no code in your post so none of the non-existant code is red ...

[expfresocspy]

Hi expfresocspy,

I think you'll need to provide a few more clues! There's no code in your post so none of the non-existant code is red ...

OUH ! Thanks for informing . I didn't notice my codes didnt appear .
Here it is :


Dim rData2 As Range, rCell2 As Range

Set rData2 = Nothing
On Error Resume Next
Set rData2 = ActiveSheet.Columns("AD"). _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rData2 Is Nothing Then Exit Sub

For Each rCell2 In rData2.Cells
Select Case rCell2.Value
Case (school name starting with "Kindergarten of")
rCell2.Offset(0, 1).Value = "Kindergarten"
Case (school name starting with "College of")
rCell2.Offset(0, 1).Value = "College"
Case (school name starting with "University of")
rCell2.Offset(0, 1).Value = "University"
Case Else
rCell2.Offset(0, 1).Value = "Others"
End Select
Next


The highlighted in red font is the one I want to change to suit the condition. How do I go about doing it ?

[Paul_Hossler]

Not really tested, but maybe something along these lines?



Option Explicit
Sub test()
Dim vPieces As Variant
Dim rData2 As Range, rCell2 As Range
Set rData2 = Nothing
On Error Resume Next
Set rData2 = ActiveSheet.Columns("AD").SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If rData2 Is Nothing Then Exit Sub

For Each rCell2 In rData2.Cells

vPieces = Split(rCell2.Value, " ")

If UBound(vPieces) >= 1 Then

Select Case LCase(vPieces(0) & " " & vPieces(1))
Case "kindergarten of"
rCell2.Offset(0, 1).Value = "Kindergarten"

Case "college of"
rCell2.Offset(0, 1).Value = "College"

Case "university of"
rCell2.Offset(0, 1).Value = "University"

Case Else
rCell2.Offset(0, 1).Value = "Others"
End Select
End If
Next
End Sub


Paul

[Aflatoon]

Why not use If...Else If instead:

For Each rCell2 In rData2.Cells
strCheck = LCase$(rCell2.Value)
If strCheck Like "kindergarten of*" Then
rCell2.Offset(, 1).Value = "Kindergarten"
ElseIf strCheck Like "college of*" Then
rCell2.Offset(, 1).Value = "College"
ElseIf strCheck Like "university of*" Then
rCell2.Offset(, 1).Value = "University"
Else
rCell2.Offset(, 1).Value = "Others"
End If
Next

[expfresocspy]

Thanks Aflatoon and Paul !
the code worked (:

[macropod]

I'd be inclined to throw in a bit more error-checking:
For Each rCell2 In rData2.Cells
With rCell2
If Len(Trim(.Value)) > 0 Then
strCheck = Split(Trim(LCase$(.Value)), " ")(0)
Else
strCheck = ""
End If
Select Case strCheck
Case "kindergarten"
.Offset(, 1).Value = "Kindergarten"
Case "college"
.Offset(, 1).Value = "College"
Case "university"
.Offset(, 1).Value = "University"
Case Is = ""
.Offset(, 1).Value = "N/A"
Case Else
.Offset(, 1).Value = "Others"
End Select
End With
Next

[expfresocspy]

I'd be inclined to throw in a bit more error-checking:
For Each rCell2 In rData2.Cells
With rCell2
If Len(Trim(.Value)) > 0 Then
strCheck = Split(Trim(LCase$(.Value)), " ")(0)
Else
strCheck = ""
End If
Select Case strCheck
Case "kindergarten"
.Offset(, 1).Value = "Kindergarten"
Case "college"
.Offset(, 1).Value = "College"
Case "university"
.Offset(, 1).Value = "University"
Case Is = ""
.Offset(, 1).Value = "N/A"
Case Else
.Offset(, 1).Value = "Others"
End Select
End With
Next

thank you so much for the help ! i havent try the codes yet but I dont really understand [because im still learning :D] this code :

With rCell2
If Len(Trim(.Value)) > 0 Then
strCheck = Split(Trim(LCase$(.Value)), " ")(0)
Else
strCheck = ""

I hope you can explain to me how the codes work (:

[macropod]

The expression 'If Len(Trim(.Value)) > 0 Then' tests whether there's anything more than just spaces in the cell.

The expression 'strCheck = Split(Trim(LCase$(.Value)), " ")(0)' says to get the first word in the cell.

The expression 'strCheck = ""' says to clear strCheck when the cell contains no 'meaningful' text.

[expfresocspy]

The expression 'If Len(Trim(.Value)) > 0 Then' tests whether there's anything more than just spaces in the cell.

The expression 'strCheck = Split(Trim(LCase$(.Value)), " ")(0)' says to get the first word in the cell.

The expression 'strCheck = ""' says to clear strCheck when the cell contains no 'meaningful' text.

Ouuhhh ! Thankyou thankyou thankyouuu for the explanation :bow:
:cloud9:

[deedii]

Sweet I just need this. Thank you :)