Checking an array

[wardides]

Hi,

I want to check if a value is one of 20 values.#

Rather than have IF value = 1 OR value = 2 OR value = 3 etc..

is there a way to have IFvalue within {1,2,3,4,5....}

Thank

 

[Guest]

Try

Function IsIn(ByVal MyValue) As Boolean
Select Case MyValue
Case 1, 2, 3, 4, 5, 6 ' Enumerate your values here
IsIn = True
End Select
End Function

In this function IsIn returns a Boolean (default - false): if your value is
enumerated, the function returns true else it returns false.

 

[Mike Fogleman]

This will use a list of values in the workbook that has been given a Named
Range port3_0. It will loop through values in column B of the ActiveSheet
and will delete the rows that match.

Sub Filter3_0()
Dim myarray As Variant
myArray = ThisWorkbook.Names("port3_0").RefersToRange.Value
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

FirstRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If IsError(Application.Match(Cells(iRow, "B").Value, myArray, 0))
Then
'do nothing
Else
Rows(iRow).Delete
End If
Next iRow
End Sub

It's a start. Modify to suit your needs.
Mike F

 

[Bob Phillips]

slight variation that may be more appropriate for you

testval1 = 17
Select Case True
Case testval1: MsgBox "1"
Case testval2: MsgBox "2"
End Select


--

HTH

RP
(remove nothere from the email address if mailing direct)