How to check if a range contains a certain value

[Luc]

Hello,

A newbie question

I have a range named GRID (3 columns x 10 rows).
Can someone give me some code with which i can check if there is a certain
value in that range.

Example :

I want to check if the GRID contains the value A.
IF it contains A THEN some actions have to follow.

If the GRID contains the value B
some other actions have to follow.

..........

..........


If the GRID contains the value Z
some other actions have to follow.

Thanx for your replies

 

[Guest]

What I do is something like this

For each c in (range)
if c.value = "A" then
else
end if
next

 

[Dave Peterson]

dim myRng as range
set myrng = worksheets("sheet1").range("Grid")
if application.countif(myrng,"A")>0 then
'it's there
else
'it's not there
'same check for B....
end if

If I were checking A to Z, I'd use something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim iCtr As Long

Set myRng = Worksheets("sheet1").Range("Grid")

For iCtr = Asc("A") To Asc("Z")
If Application.CountIf(myRng, Chr(iCtr)) > 0 Then
'found it
'do what you want
MsgBox "Found: " & Chr(iCtr)
Exit For 'stop looking
End If
Next iCtr

End Sub