Select Case

G

Guest

I would like to apply the "Select Case" to a whole column instead of a single
cell. Could anyone please show me the way. For more information, I apply
the function to a single cell by the followings:-

Sub Test()
Case Is > 80
Range("b1").Value = "Good"
Case Is > 60
Range("b1").Value = "Fair"
Case Else
Range("b1") = "Unclassified"
End Select
End Sub

Thank you
 
N

NickHK

Post the code you actually use, because what you have there will not
compile; there's no "Select Case ..." statement, so we have no idea what you
are trying to test.
However, I suspect a worksheet formula or conditional formatting would
achieve your goal.

NickHK
 
G

Guest

NickHK, you are right. It should be as follows:-

Sub Test()
Select Case Range("A1")
Case Is > 80
Range("b1").Value = "Good"
Case Is > 60
Range("b1").Value = "Fair"
Case Else
Range("b1") = "Unclassified"
End Select
End Sub

Do you think it is possible to modify it in order to apply it to a whole
column instead of a cell only?
"NickHK" 來函:
 
N

NickHK

Not sure where the column comes in ?

You mean test each cell in a column ?
For Each Cell In Range("A1:A10")
Select Case Cell.Value
Case Is > 80
Cell.Offset(0, 1).Value = "Good"
Case Is > 60
Cell.Offset(0, 1).Value = "Fair"
Case Else
Cell.Offset(0, 1).Value = "Unclassified"
End Select
Next

Unless you feel you may expand the number of comparisons in the future, you
can achieve the result with a formula, then fill down:
=IF(A1>80,"Good",IF(A1>60,"Fair","Unclassified"))

NickHK
 

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

Top