CODEWITHSUNDEEP
vbaexcelselectcase
evolancer
evolancer

Reputation: 53

Excel VBA - Select Case - Multiple Test Expressions

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"

Upvotes: 0

Views: 3648

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

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

Upvotes: 2

Related Questions