Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equivalent of Alt+Tab for going between worksheets? | Excel Discussion (Misc queries) | |||
equivalent for function , please for help | Excel Worksheet Functions | |||
VBA equivalent for worksheet 'match'? | Excel Discussion (Misc queries) | |||
Maxif equivalent | Excel Worksheet Functions | |||
How turn on excel functn, dbleclick on cell 2 show reference? | Setting up and Configuration of Excel |