How do I compare a single (but variable) value (say Z)to
a set (fixed) list of values to determine whether Z
appears in that list?
[Specific case is a date that varies and a list of dates
that doesn't change - such as public holidays]

Assuming Z is the value of cell A1 and your list starts in
cell B1 extending downwards:


Range("A1").Select
dte = ActiveCell.Value2

Range("B1").Select
Do Until IsEmpty(ActiveCell) = True
If ActiveCell.Value2 = dte Then
MsgBox ("Date matches list entry")
Exit Do 'or whatever you want to do
End If
ActiveCell.Offset(1, 0).Select
Loop

This will run to the bottom of the list if the next cell
down is blank.
Alternatively, if the next cell down is not empty but
containsanything but a date, you can substitute the Do
Until expression with the following one:

Do While IsDate(ActiveCell) = True

If neither option above works for you, but you know the
size of the list and it never changes, you could use a do
loop instead, either hardcoding the number of iterations,
or reading it from a cell in the worksheet (not a neat
solution). To do this you would cubstitute the "Do Until"
line with a "For i = 1 to lstl - 1" (lstl being the list
length) the "Exit Do" line with a "Exit For" one and
the "Loop" line with a "Next" one.

Nikos Y.
-----Original Message-----
How do I compare a single (but variable) value (say Z)to
a set (fixed) list of values to determine whether Z
appears in that list?
[Specific case is a date that varies and a list of dates
that doesn't change - such as public holidays]
.

Bob,

An easy way is to simply iterate through your list:

Sub TryNow()
Dim myList(1 To 3) As Date
Dim Z As Date
Dim Present As Boolean
Dim i As Integer

myList(1) = DateValue("1/2/3")
myList(2) = DateValue("1/3/3")
myList(3) = DateValue("1/4/3")

Z = DateValue("1/4/3")

'Code here to set the list
Present = False
For i = LBound(myList) To UBound(myList)
If Z = myList(i) Then
Present = True
End If
Next i
If Present Then
MsgBox "Found It"
Else
MsgBox "Didn't Find It"
End If

End Sub

HTH,
Bernie


"Bob F" wrote in message
...
How do I compare a single (but variable) value (say Z)to
a set (fixed) list of values to determine whether Z
appears in that list?
[Specific case is a date that varies and a list of dates
that doesn't change - such as public holidays]

Bernie/Anybody,

Re. Bernie's response, how does this work if there is already one
For... loop in place finding the initial value?

For example. I have a procedure that loops through all the cells in a
range. However, when certain conditions are met (using If's) I need to
then search another range of cells to see if any of them match the
current cell value?

This may be able to be done with nested For... statements but I'm
afraid my experience currently restricts me to being able to compose
singe For... statements, not nested ones!

Regards,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Something like:

Option Explicit
Sub testme()

Dim myRng1 As Range
Dim myRng2 As Range
Dim myCell1 As Range
Dim myCell2 As Range

Set myRng1 = Worksheets("sheet1").Range("a1:b99")
Set myRng2 = Worksheets("sheet3").Range("c1:c3")

For Each myCell1 In myRng1.Cells
If something_happens_good Then
For Each myCell2 In myRng2.Cells
If myCell2.Value = myCell1.Value Then
'do more stuff
End If
Next myCell2
End If
Next myCell1

End Sub

Another way to see if a value is contained in a range is by using a worksheet
function:

if application.countif(myrng2,mycell1.value) 0 then
'it's in there
end if

(only if you're just looking to see if it's there.)

Maybe using .find (see VBA's help) would be more efficient--if you were only
looking for the first match.




Kobayashi wrote:

Bernie/Anybody,

Re. Bernie's response, how does this work if there is already one
For... loop in place finding the initial value?

For example. I have a procedure that loops through all the cells in a
range. However, when certain conditions are met (using If's) I need to
then search another range of cells to see if any of them match the
current cell value?

This may be able to be done with nested For... statements but I'm
afraid my experience currently restricts me to being able to compose
singe For... statements, not nested ones!

Regards,

Adrian

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

Dave,

Many thanks. I tried to use the nested For Each statements but although
this works if you are looking to match a value I'm looking to see if
the value is NOT in the list and so I couldn't get this to work for
that?
However, the If function you also kindly supplied works perfectly as I
can, and I hope this is right?, simply change the '1' to '=0' to
search for an item not in the list.
So, many thanks indeed, this has helped solve one big, long
headache!!!

Regards,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

There are lots of ways to check to see if a value is in a list. You did right
with the =0 stuff.



Kobayashi wrote:

Dave,

Many thanks. I tried to use the nested For Each statements but although
this works if you are looking to match a value I'm looking to see if
the value is NOT in the list and so I couldn't get this to work for
that?
However, the If function you also kindly supplied works perfectly as I
can, and I hope this is right?, simply change the '1' to '=0' to
search for an item not in the list.
So, many thanks indeed, this has helped solve one big, long
headache!!!

Regards,

Adrian

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson