does range value exist in another range
 

is there a simple macro i can run to determine if the value in range("B5")
exists anywhere in range("A1:A10").
similar to the excel function
=MATCH(B5, A1:A10,0)
TIA.

does range value exist in another range
 

You could use FIND
or just
application.match(range("b5"),range(a1:a10),0)

--
Don Guillett
SalesAid Software

"Spencer" wrote in message
...
is there a simple macro i can run to determine if the value in range("B5")
exists anywhere in range("A1:A10").
similar to the excel function
=MATCH(B5, A1:A10,0)
TIA.

does range value exist in another range
 

Sub Macro1()
Range("C5").FormulaR1C1 = _
"=MATCH(RC[-1], R[-4]C[-2]:R[5]C[-2],0)"
If IsNumeric(Range("C5").Value) Then
MsgBox "Found"
Else
MsgBox "Not found"
End If
End Sub

Hth,
Merjet

does range value exist in another range
 

i don't want to enter a formula in a cell, i want to return the value
true/false back to a variable called "VARIABLE". True being the value in B5
was found in column A, or False meaning it was not.

"merjet" wrote:

Sub Macro1()
Range("C5").FormulaR1C1 = _
"=MATCH(RC[-1], R[-4]C[-2]:R[5]C[-2],0)"
If IsNumeric(Range("C5").Value) Then
MsgBox "Found"
Else
MsgBox "Not found"
End If
End Sub

Hth,
Merjet

does range value exist in another range
 

Dim Variable as Boolean, res as Variant
res = application.match(range("b5"),range("a1:a10"),0)
variable = not iserror(res)

--
Regards,
Tom Ogilvy


"Spencer" wrote:

i don't want to enter a formula in a cell, i want to return the value
true/false back to a variable called "VARIABLE". True being the value in B5
was found in column A, or False meaning it was not.

"merjet" wrote:

Sub Macro1()
Range("C5").FormulaR1C1 = _
"=MATCH(RC[-1], R[-4]C[-2]:R[5]C[-2],0)"
If IsNumeric(Range("C5").Value) Then
MsgBox "Found"
Else
MsgBox "Not found"
End If
End Sub

Hth,
Merjet

does range value exist in another range
 

Sub Macro1()
Dim VARIABLE As Boolean
x = Application.Match(Range("A5"), Range("A1:A10"), 0)
If TypeName(x) < "Error" Then VARIABLE = True
End Sub

Hth,
Merjet