CODEWITHSUNDEEP
vbaexcel
F1990
F1990

Reputation: 637

Multiple statements in Select Case vba

I would like to use the Select Case option in excel vba to filter the right data. I do the following : 

For i = 2 To LastRow  

    StartTime = Worksheets("Task input").Cells(1 + i, 9)
    Status = Worksheets("Task input").Cells(1 + i, 14)

    Select Case StartTime And Status

           Case TimeWindowBound1 To TimeWindowBound2 And "Planned"
                Worksheets("Performance output").Cells(5, 2) = Worksheets("Performance output").Cells(5, 2) + 1

    End Select

Next i

He compares the StartTime and the Status. However this give an error? Any solution?

Upvotes: 0

Views: 5174

Answers (2)

R.Katnaan
R.Katnaan

Reputation: 2526

Really, I am surprise with your code. I haven't found anything like this.

You need to know one thing is that SELECT CASE [condition] AND [condition] cannot use in any where.

But you need to check both, so try as follow:

Public Sub testSelect()

    Dim sDate As Date
    Dim sString As String

    'Set test date
    sDate = "10/02/2014"

    'Set test string
    sString = "select"

    'First select date, because it need to check in a range
    Select Case sDate

        'If it is in desire range, check string
        Case "10/01/2014" To "10/30/2014"

            'If string is equal, show message for True.
            If sString = "select" Then
                MsgBox ("True")

            'Else show message for False
            Else
                MsgBox ("False")
            End If

    End Select

End Sub

Upvotes: 0

Maxime Porté
Maxime Porté

Reputation: 1074

You can't do that directly in the select case

(documentation of Select case https://msdn.microsoft.com/en-us/library/cy37t14y.aspx)

The select case can work only on a single variable. But here the solution is not really complicated:

Select Case StartTime
Case TimeWindowBound1 To TimeWindowBound2
    If Status = "Planned" then Worksheets("Performance output").Cells(5, 2) = Worksheets("Performance output").Cells(5, 2) + 1
End Select

Upvotes: 3

Related Questions