Equivalent of Match Functn in VBA

  • Thread starter Thread starter blueshot
  • Start date Start date
B

blueshot

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
 
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
 
Back
Top