VBA Select Case

[ekw590]

My question is a continuation from this post: http://social.answers.microsoft.com/Forums/en-US/officeprog/thread/3328dc06-0b87-4447-a3e0-083be3bc1cdb because I so confused the issue I’m restarting.

I have a column of metal codes as 2 character strings to be grouped as A, B, or C. The first leg of the “If … Then” loop tests for category “A” based on metal codes: "1?", "2T", "4B", "4C", "4E", "4F", "4G", "4J", "4L", "4R", "4S", "4T", "4U", "4W", "4X", "4Y", "8P", "8T", "8U", "8W", "8X", "8Y", "8Z" I assumed that the wildcard character “*” could be used as: “1*”, “4*” & “8*” and HansV proposed a great macro wherein I found an error on my assumption. While the code “1” may take the wildcard “*” “4” and “8” may not because of many variations with the metals that places them in B or C category.

In place of the If… Then procedure I wrote a Select Case procedure but I run into a problem: groups B and C are contingent on whether column G contains a P for the B group or M or the C group (Group A may take either). Can the 2nd variable testing for P or M in column G be nested into the Select Case code here.

Sub KCCodeSelect()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Range("H:H").Insert
Range("H1") = "KC Code"
m = Range("F" & Rows.Count).End(xlUp).Row
For r = 2 To m
Select Case Range("F" & r)
Case "1?", "2T", "4B", "4C", "4E", "4F", "4G", "4J", "4L", "4R", "4S", "4T", "4U", "4W", "4X", "4Y", "8P", "8T", "8U", "8W", "8X", "8Y", "8Z"
Range("H" & r) = "A"
Case "A*", "CO", "ET", "LD", "P*", "RR", "S*", "T*", "XX"
Range("H" & r) = "B"
Case "A*", "CO", "ET", "LD", "P*", "RR", "S*", "T*", "XX", "4)", "8)", "8&", "8-", "8/", "8>", "8\", "8¦", "8<", "8}", "8H", "8I", "8O", "AP", "TP", "TR"
Range("H" & r) = "C"
End Select
Next r
Application.ScreenUpdating = True
End Sub



Sub KCCodeSelect()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Range("H:H").Insert
Range("H1") = "KC Code"
m = Range("F" & Rows.Count).End(xlUp).Row
For r = 2 To m
Select Case Range(“F” & r)
Case “1*”, “2T”, “4B”, “4C”, “4E”, “4F”, “4G”, “4J”, “4L”, “4R”, “4S”, “4T”, “4U”, “4W”, “4X”, “4Y”, “8P”, “8T”, “8U”, “8W”, “8X”, “8Y”, “8Z”
Range("H" & r) = "A"
Case “A*”, ”CO”, “ET”, “LD”, “P*”, “RR”, “S*”, “T*”, “XX”
Range("H" & r) = "B"
Case “A*”, “CO”, “ET”, “LD”, “P*”, “RR”, “S*”, “T*”, “XX” , “4)”, “8)”, “8&”, “8-“, “8/”, “8>”, “8\”, “8¦”, “8<”, “8}”, “8H”, “8I”, “8O”, “AP”, “TP”, “TR”
Range("H" & r) = "C"
End Select
Next r
Application.ScreenUpdating = True
End Sub

Thanks EVan

[Bob Phillips]

Just nest a second select within the first.

[ekw590]

Just Exactly how to write a nested Case Select was part of my problem. Is it necessary to add a Case Else before the nested Case Select? Is this correct?


Sub KCCodeSelect()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Range("H:H").Insert
Range("H1") = "KC Code"
m = Range("F" & Rows.Count).End(xlUp).Row
For r = 2 To m
Select Case Range(“F” & r)
Case “1*”, “2T”, “4B”, “4C”, “4E”, “4F”, “4G”, “4J”, “4L”, “4R”, “4S”, “4T”, “4U”, “4W”, “4X”, “4Y”, “8P”, “8T”, “8U”, “8W”, “8X”, “8Y”, “8Z”
Range("H" & r) = "A"
Case “A*”, ”CO”, “ET”, “LD”, “P*”, “RR”, “S*”, “T*”, “XX”
‘nested select case
Select Case Range("G" & r)
Case “P”
Range("H" & r) = "B"
Case “A*”, “CO”, “ET”, “LD”, “P*”, “RR”, “S*”, “T*”, “XX” , “4)”, “8)”, “8&”, “8-“, “8/”, “8>”, “8\”, “8¦”, “8<”, “8}”, “8H”, “8I”, “8O”, “AP”, “TP”, “TR”
‘nested select case
Select Case Range("G" & r)
Case “M”
Range("H" & r) = "C"

End Select
End Select
End Select
Next r
Application.ScreenUpdating = True
End Sub


Thanks EVan

[Bob Phillips]

It would be along the lines of



Select Case condition1

Case "A":

Select Case condition2

Case 1: 'do something

Case 2; 'do another thing

Case Else: ' do the deault
End Select

Case "B":

'....
End Select

[ekw590]

Thanks