Select Case on a range

Question

I basically have multiple variables that I want to categorize. I have a list of which variable goes into which category however given I have 40,000 entries doing it manually would be timely.

Basically my data is such that a,b,c are in Group A, d,e,f in Group B and so on. I was trying to use the Select Case but i can't figure out how to run it on a range; meaning, I have variables in column A. I want it to check each cell in column A and suppose if its d then write "Group B" in column B. This is all i could manage as a beginner

 Case "a", "b", "c"
 cells(1,2).Value = "Group A"
 Case Else
 cells(1,2).Value = "Not Defined"
 End Select
 End Sub

YowE3K · Accepted Answer · 2017-03-04T20:33:08.133

3

You need a loop:

    Dim r As Long
    For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        Select Case Cells(r, "A").Value
            Case "a", "b", "c"
                Cells(r, 2).Value = "Group A"
            Case "d", "e", "f"
                Cells(r, 2).Value = "Group B"
            Case Else
                Cells(r, 2).Value = "Not Defined"
        End Select
    Next
End Sub

edited Mar 04 '17 at 20:33

answered Mar 04 '17 at 20:31

YowE3K

23,437
7
24
37

Thanks! Just realised that! – Isra Shaikh Mar 04 '17 at 20:32

score 0 · Answer 2 · answered Mar 05 '17 at 04:45

Rather than loop cell by cell you can run a quicker VBA equivalent of

=IF(OR(A2={"a","b","c"}),"Group A",IF(OR(A2={"d","e","f"}),"Group B","not me"))

The Value part is the same as doing a copy paste special value, i.e. it removes the formulae

Dim rng1 As Range
With ActiveSheet
    Set rng1 = .Range(.[a1], .Cells(.Rows.Count, "a").End(xlUp))
    rng1.Offset(0, 1).FormulaR1C1 = "=IF(OR(RC[-1]={""a"",""b"",""c""}),""Group A"",IF(OR(RC[-1]={""d"",""e"",""f""}),""Group B"",""not me""))"
    rng1.Offset(0, 1).Value = rng1.Offset(0, 1).FormulaR1C1
End With

Select Case on a range

2 Answers2