How to "translate" that in VBA ?
 

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

--
Message posted from http://www.ExcelForum.com

How to "translate" that in VBA ?
 

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.
--
Regards,
Tom Ogilvy

"Grek " wrote in message
...
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 criterias
(dsfdskf, aaaa,...) in an an other excell sheet (sheet2) and do
something in VBA like

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

Could someone help me to do that ?

Many thanks in advance,

Greg


---
Message posted from http://www.ExcelForum.com/

How to "translate" that in VBA ?
 

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


"Grek " wrote in message
...
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 criterias
(dsfdskf, aaaa,...) in an an other excell sheet (sheet2) and do
something in VBA like

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

Could someone help me to do that ?

Many thanks in advance,

Greg


---
Message posted from http://www.ExcelForum.com/