CODEWITHSUNDEEP
vbaexcel
The B
The B

Reputation: 45

Using AND in VBA excel with Select case

I am trying to write a simple function that give me band 1 if value is zero Band 2 if its between 0 to 150k Band 3 if between 150 k to 500k and so on

I only get 1 if value is zero and everything else is 2 , even if I enbter 10 million

Please help 

Function Band(ARR)

    Select Case ARR

        Case Is = 0
            Band = 1

        Case Is >= 0.1 And ARR <= 150000
            Band = 2

        Case Is >= 150000.01 And ARR <= 500000
            Band = 3

        Case Is >= 500000.01 And ARR <= 1500000
            Band = 4

        Case Is >= 1500000.01
            Band = 5

        Case Else
            Band = 6

    End Select

End Function

Upvotes: 0

Views: 1888

Answers (2)

WorkSmarter
WorkSmarter

Reputation: 3808

You could use the "To" keyword to specify a range. See here for more information.

Function Band(arr)
Select Case arr
  Case 0
    Band = 1
  Case 0.01 To 150000
    Band = 2
  Case 150000.01 To 500000
    Band = 3
  Case 500000.01 To 1500000
    Band = 4
  Case Is >= 1500000.01
    Band = 5
  Case Else
    Band = 6
  End Select
End Function

Upvotes: 4

user4039065
user4039065

Reputation:

VBA Select Case 'short-circuits' (i.e. no further cases are examined) once a matching criteria is met so there is no need for And operators.

Function Band(ARR)
    Select Case true
        Case ARR < 0
            Band = 6
        Case arr = 0
            Band = 1
        Case arr <= 150000
            Band = 2
        Case arr <= 500000
            Band = 3
        Case arr <= 1500000
            Band = 4
        Case else
            Band = 5
    End Select
End Function

Upvotes: 4

Related Questions