Excel Select Case Type Mismatch Error

Question

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.

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 or deal with the average, mean, sum, etc. — , Apr 21 '16 at 16:25
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, Apr 21 '16 at 16:25
@findwindow - It's done to load a range of cells' values into a variant array quite commonly. — , Apr 21 '16 at 16:48

score 3 · Accepted Answer · answered Apr 21 '16 at 16:28

3

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.

answered Apr 21 '16 at 16:28

yk11

768
4
9

2

FWIW: I would find the last occupied cell in column Q, just so it does not iterate through 1M+ rows. But yeah this is the gist of the issue. – Scott Craner Apr 21 '16 at 16:30
This is exactly what I needed to know. It works perfectly. – user1916528 Apr 21 '16 at 16:57

score 1 · Answer 2 · answered Apr 21 '16 at 17:03

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

Excel Select Case Type Mismatch Error

2 Answers2