Excel VBA - Select Case - Multiple Test Expressions

Question

I am attempting to write a Select Case Statement that looks at a range of values throughout the A column and then preforms the statements.

So far I can get my select case statement to work for row A2 but I can't get it to work with a range of values without giving me a, 'Type Mismatch Error'.

Working

Select Case range("A2").Value

Case "01"
    range("G2").Value = "Admin"
    range("I2").Value = "None"
    range("J2").Value = "None"
    range("K2").Value = "None"
    range("H2").Value = "No Affiliation"
    range("T2").Value = "99999"

Not Working (Type Mismatch Error)

Select Case Range ("A2:A1000").Value

Case "01"
    range("G2").Value = "Admin"
    range("I2").Value = "None"
    range("J2").Value = "None"
    range("K2").Value = "None"
    range("H2").Value = "No Affiliation"
    range("T2").Value = "99999"

Case "A"
    range("G2").Value = "Dealer"
    range("I2").Value = "None"
    range("J2").Value = "None"
    range("K2").Value = "None"

SierraOscar SierraOscar · Accepted Answer · 2014-11-17T14:35:01

You need to step through each cell in the range instead, "A2:A1000" will return an array, not a value.

Sub SO()

Dim rNum As Long

For rNum = 2 To 1000

   Select Case Range("A" & rNum).Value
      Case "01"
         Range("G" & rNum).Value = "Admin"
         Range("I" & rNum).Value = "None"
         Range("J" & rNum).Value = "None"
         Range("K" & rNum).Value = "None"
         Range("H" & rNum).Value = "No Affiliation"
         Range("T" & rNum).Value = "99999"

      Case "A"
         Range("G" & rNum).Value = "Dealer"
         Range("I" & rNum).Value = "None"
         Range("J" & rNum).Value = "None"
         Range("K" & rNum).Value = "None"

      Case Else
         '// Do something else...
   End Select

Next rNum

End Sub

Excel VBA - Select Case - Multiple Test Expressions

1 Answers