CODEWITHSUNDEEP
excelvba
watercoder
watercoder

Reputation: 13

Setting a Variable to have multiple possible values

Trying to get a macro to do the same function in multiple sheets and I was thinking I could use this sort of code to select the data I wanted:

Sub Foo
Dim OtherStuff As Things
Dim Thing1 as Variant

Thing1 = "a" Or "b" Or "c"

'more things

If cell.value = Thing1 Then
Do stuff
End If
Next Cell
End Sub

I keep getting hit with the good ol' happytime "Run-time error '13': Type Mismatch".

Am I trying to do something that variant won't do? Is there a way to do it that I just haven't learned yet? I've looked around a good bit and haven't found much. Thanks for the help!

Upvotes: 1

Views: 13612

Answers (3)

Dan Donoghue
Dan Donoghue

Reputation: 6206

You code is very close, you need Thing1 to be an array (You already defined it correctly as a variant for this). Then you just loop through the elements.

Here is the modified code. Pretty simple to follow but post back if you have questions:

Sub Foo()
'Dim OtherStuff As things
Dim Thing1 As Variant, Cell As Range

Thing1 = Array("a", "b", "c")
'more things
For Each Cell In Range("A1:A10")
    For Each Thing In Thing1
        If Cell.Value = Thing Then
            'Do stuff
        End If
    Next Thing
Next Cell
End Sub

Upvotes: 0

Rob Cutmore
Rob Cutmore

Reputation: 477

In response to your comment to the answer provided by matrixugly you can maintain a boolean variable at the top of the subroutine. If you need to add or remove cases you can manage it there.

Sub Foo()
    Dim valueMatches As Boolean
    valueMatches = cell.value = "a" Or _
                   cell.value = "b" Or _
                   cell.value = "c"

    ' ...more code...

    If valueMatches Then
        ' Do stuff
    End If
End Sub

You could also do the check in a separate function and maintain any changes there.

Private Function ValueMatches(ByVal value As String) As Boolean
    ' Maintain values to match in this single function.
    ' Note: Function will need to be public instead of
    ' private if stored in another module.
    ValueMatches = value = "a" Or value = "b" Or value = "c"
End Function

Sub Foo()
    ' ...more code...

    If ValueMatches(cell.value) Then
        ' Do stuff
    End If
End Sub

Upvotes: 0

C. Tewalt
C. Tewalt

Reputation: 2509

You should change your code so it's something like this. You can't set a variable to multiple values, but you can have a condition which checks for multiple values.

Not the slickest way, but it will work.

Sub Foo
Dim OtherStuff As Things
Dim Thing1 as Variant

' won't work -> Thing1 = "a" Or "b" Or "c"

'more things

'this should work
If cell.value = "a" Or cell.value = "b" cell.value = "c" Then
Do stuff
End If
Next Cell
End Sub

Upvotes: 1

Related Questions