multiple qualifiers for If statements

[Matthew Dyer]

If Cells(i, "e").Value = "CA" Then

I want to include all west coast accounts with the same if statement.

If Cells(i, "e").Value = "CA" Or "WA" Or "OR" Or "NV" Then -- this
of course isn't working. How would I get it to work?

 

[Tim Williams]

if not iserror(application.matchCells(i, "e").Value ,
array("CA","WA","OR","NV"),0)) then
.....

Note: this is not case-sensitive: that might or might not matter for you...

Tim

 

[Matthew Dyer]

if not iserror(application.matchCells(i, "e").Value ,
array("CA","WA","OR","NV"),0)) then
....

Note: this is not case-sensitive: that might or might not matter for you....

Tim








- Show quoted text -

I get an error saying "Wrong number of arguments or invalid property
assignments"...

 

[Tim Williams]

Should all be on one line (and I missed the opening parenthesis for Match())

Sub tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A1")
If Not IsError(Application.Match(rng.Value, _
Array("CA", "WA", "OR", "NV"), 0)) Then

MsgBox "Value OK"

End If

End Sub


Tim

if not iserror(application.matchCells(i, "e").Value ,
array("CA","WA","OR","NV"),0)) then
....

Note: this is not case-sensitive: that might or might not matter for
you...

Tim








- Show quoted text -

I get an error saying "Wrong number of arguments or invalid property
assignments"...

 

[Matthew Dyer]

Should all be on one line (and I missed the opening parenthesis for Match())

Sub tester()
Dim rng As Range

    Set rng = ActiveSheet.Range("A1")
    If Not IsError(Application.Match(rng.Value, _
                          Array("CA", "WA", "OR", "NV"), 0)) Then

        MsgBox "Value OK"

    End If

End Sub

Tim






I get an error saying "Wrong number of arguments or invalid property
assignments"...- Hide quoted text -

- Show quoted text -

Works perfectly. Thanks for the help!

On a side note... Is it considered sloppy coding to use the same
method to build arrays within the if statements or should I build the
arrays in a public statement, then reference the arrays within the if
statements?

 

[Tim Williams]

If you are using the array in multiple places then you should probably code
a small function to encapsulate it:

Function TheStates()
TheStates=Array("CA", "WA", "OR", "NV")
End Function

t once then I wouldn't bother, but it really depends on the "target
audience" (in-house utility app vs. commercial code) and how much
maintenance you expect to be doing. The more maintenance, the more you want
to isolate variables into constants or methods.

Tim

Should all be on one line (and I missed the opening parenthesis for
Match())

Sub tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A1")
If Not IsError(Application.Match(rng.Value, _
Array("CA", "WA", "OR", "NV"), 0)) Then

MsgBox "Value OK"

End If

End Sub

Tim






I get an error saying "Wrong number of arguments or invalid property
assignments"...- Hide quoted text -

- Show quoted text -

Works perfectly. Thanks for the help!

On a side note... Is it considered sloppy coding to use the same
method to build arrays within the if statements or should I build the
arrays in a public statement, then reference the arrays within the if
statements?