Excel Select Case Type Mismatch Error

1
votes

I am working on a simple Select Case in Excel 2013. I want the value in column R to be based on the value in column Q. But I get a type mismatch error. This is a shortened version of cases since, there are actually 20, but here is what I'm using to test (the following presents the type mismatch error):

Sub laranges()
Select Case Range("Q:Q").Value
    Case 0 To 0.49
    Range("R:R").Value= "Low"
    Case 0.5 To 0.99
    Range("R:R").Value= "Medium"
    Case Else
    Range("R:R").Value = "High"
End Select

End Sub

The following works if I change it to select only one cell (I have tested it on several cells and they were all successful individually):

Sub laranges()
Select Case Range("Q2").Value
    Case 0 To 0.49
    Range("R2").Value = "Low"
    Case 0.5 To 0.99
    Range("R2").Value = "Medium"
    Case Else
    Range("R2").Value = "High"
End Select

End Sub

Obviously I'm not referencing the ranges correctly. I have also tried changing the range to Q2:Q3 and R2:R3, but I get the same error. But if I reference Q2 and Q3 individually they run successfully.

How can I properly reference the ranges? I have looked at Ozgrid, MSDN, as well as several threads on SO but I just haven't been successful.

2
vbaexcel
You can't grab value from more than 1 cell. - findwindow
Well, you actually can grab more than a single cell's .Value or .Value2 but you end up with an array that cannot be dealt with as a single value. Loop through the values in Q2:Q<last populated cell> or deal with the average, mean, sum, etc. - user4039065
What's going to happen if Q1 has ".3" and Q2 has ".99"? What's your sub supposed to do in that case?? That's why there's a mismatch error. - BruceWayne
@Jeeped oooo good to know! - findwindow
@findwindow - It's done to load a range of cells' values into a variant array quite commonly. - user4039065

2 Answers

3
votes

You need to iterate on the range

Sub laranges()
Dim sResult As String
For Each oCell In Range("Q:Q")
    Select oCell.Value
        Case 0 To 0.49
            sResult = "Low"
        Case 0.5 To 0.99
            sResult = "Medium"
        Case Else
            sResult = "High"
    End Select

    oCell.Offset(0, 1).Value = sResult
Next oCell

Putting this type of logic in the s/s itself would be much more efficient.

1
votes

you could try this "formula" approach

Sub main()
' for "Choose" function clever use, see tobriand's answer in http://stackoverflow.com/questions/5488692/case-function-equivalent-in-excel

With ActiveSheet.Range("Q:Q").SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, 1)

    .FormulaR1C1 = "=CHOOSE(   1   +   LOG(1 + (2*AND(RC[-1]>=0,RC[-1]<=0.49) ) + (4*AND(RC[-1]>=0.5,RC[-1]<=0.99)) ,2)," _
                     & "    ""High"" ,                 ""Low"",                          ""Medium"")"
    .Value = .Value

End With

End Sub