How to "translate" that in VBA ?

[Grek]

Hi,

At the moment I have something like this :


/////////////////////////////////////////////////////////////////


If ActiveCell.Value = "3" Then
ActiveCell.Value = "Ok"
If ActiveCell.Value = "dsfdskf" Then
ActiveCell.Value = "Ok"
If ActiveCell.Value = "aaaa" Then
ActiveCell.Value = "Ok"
If ActiveCell.Value = "dbbbb" Then
ActiveCell.Value = "Ok"
End If
End If
End If
End If

//////////////////////////////////////////////////////////////////


As it is not very efficient, I would like to put all my criteria
(dsfdskf, aaaa,...) in an an other excell sheet (sheet2) and d
something in VBA like

If ActiveCel.value = any of the value from Range A1 to A30 of sheet
Then
ActiveCell.Value - "ok"
End if

Could someone help me to do that ?

Many thanks in advance,

Gre

 

[Tom Ogilvy]

Dim res as variant
res = Application.Match(activecell.Value, worksheets("Sheet3") _
.Range("A1:A50"),0)
if not iserror(res) then
activecell.value = "Ok"
end if


note that "3" <> 3 so if you might have a number 3 in the activecell, then
you need to include both a 3 and "3" in the lookup range.

 

[Cecilkumara Fernando]

Grek,
something like this

Dim rng As String
Dim l As String
rng = "Sheet2!$A$2:$A$31"
l = ActiveCell.Value
If Not IsError(Evaluate("Match(" & Chr(34) & l & _
Chr(34) & "," & rng & ",0)")) Then
ActiveCell.Value = "ok"
End If

Cecil