Equivalent of Match Functn in VBA
 

Hi,
I am VERY new to VBA (second day) and am trying to create code tha
will take a value and check to see if it is contained within
specified list or not, returning "duplicate" if it is and "not
duplicate" if it is.

I don't think that the =Match formula is a defined function in VBA s
it is causing an error. How can I get around this? I would like t
create this function with coding that I know so would like to keep i
similar to what I have below

Function JDuplicate(Potential_Duplcate As Double, Look_In_Range A
range) As String

'if the cell is empty the return a blank
If Potential_Duplicate = "" Then
JDuplicate = ""
Else

' Check to see if the value is contained in the specified list
If isserror(Match(Potential_Duplicate, Look_In_Range, 0)
True) Then
JDuplicate = "not a duplicate"
Else
JDuplicate = "duplicate"
End If
End If
End Function

Can anyone help me without making my solution too complex. I am sur
that there is an easier way to code it and I am open to thos
suggestions to but I would really like to find the solution I a
looking for as well.

Thanks

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

Equivalent of Match Functn in VBA
 

True, Match is not a VBA function, but you can use most of the
worksheetfunction in VBA. It is a member of the Worksheetfunction object,
however, it can also just be qualified with application. For match and
vlookup, these seem to behave better when just qualified with Application.
Also, when the function would normally return an error such as #N/A, you can
check this with Iserror when qualified with Application. When qualified
with Worksheetfunction, it raises a 1004 error which you must trap for.

Dim res as Variant
res = Application.Match(cell.Value, Range("A1:A200"),0)
if not iserror(res) then
'found
else
' not found
End if

--
Regards,
Tom Ogilvy

blueshot wrote in message
...
Hi,
I am VERY new to VBA (second day) and am trying to create code that
will take a value and check to see if it is contained within a
specified list or not, returning "duplicate" if it is and "not a
duplicate" if it is.

I don't think that the =Match formula is a defined function in VBA so
it is causing an error. How can I get around this? I would like to
create this function with coding that I know so would like to keep it
similar to what I have below

Function JDuplicate(Potential_Duplcate As Double, Look_In_Range As
range) As String

'if the cell is empty the return a blank
If Potential_Duplicate = "" Then
JDuplicate = ""
Else

' Check to see if the value is contained in the specified list
If isserror(Match(Potential_Duplicate, Look_In_Range, 0) =
True) Then
JDuplicate = "not a duplicate"
Else
JDuplicate = "duplicate"
End If
End If
End Function

Can anyone help me without making my solution too complex. I am sure
that there is an easier way to code it and I am open to those
suggestions to but I would really like to find the solution I am
looking for as well.

Thanks,


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

Equivalent of Match Functn in VBA
 

Thanks for the advice Tom. I have got it working now. The help i
greatly appreciated

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