Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking an array
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 -- wardide ----------------------------------------------------------------------- wardides's Profile: http://www.excelforum.com/member.php...fo&userid=1502 View this thread: http://www.excelforum.com/showthread.php?threadid=27447 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking an array
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. "wardides" wrote: 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....} Thanks -- wardides ------------------------------------------------------------------------ wardides's Profile: http://www.excelforum.com/member.php...o&userid=15022 View this thread: http://www.excelforum.com/showthread...hreadid=274471 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking an array
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 "wardides" wrote in message ... 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....} Thanks -- wardides ------------------------------------------------------------------------ wardides's Profile: http://www.excelforum.com/member.php...o&userid=15022 View this thread: http://www.excelforum.com/showthread...hreadid=274471 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking an array
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) "AA2e72E" wrote in message ... 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. "wardides" wrote: 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....} Thanks -- wardides ------------------------------------------------------------------------ wardides's Profile: http://www.excelforum.com/member.php...o&userid=15022 View this thread: http://www.excelforum.com/showthread...hreadid=274471 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
Make list of cells in array and checking for duplicates | Excel Worksheet Functions | |||
Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Checking Number of Dimensions In Array | Excel Programming |