Click here to Skip to main content
14,332,294 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am trying to implement below excel formula in vba macro but it's not working,please suggest me right way.
My problem is that I'm trying to find duplicate entry in same column and mark it as duplicate,but it not satisfy the condition.
How to make correct vba condition using this formula?
The formula is
=IF(ISERROR(MATCH(C2,C$2:C$16,0)),"","Duplicate")

and my condition in vb is given below
If WorksheetFunction.IsError(WorksheetFunction.Match(Sheets(1).Cells(cnt, colCnt), Sheets(1).Range("C2:C" & lastRow), 0)) Then

Here,cnt initialize to 2 and lastrow is total rows in current column
Suggest me the right way to do it
Posted
Comments
ZurdoDev 9-Oct-15 8:13am
   
I'm not sure what part you are stuck on. Also, you can record a macro doing what you want and you can see the code.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If you want to insert formula into C2 cell, try this:
Sheets(1).Range("C2").Formula = "=IF(ISERROR(MATCH(C" & cnt & ",C$2:C$" & lastrow & ",0)),'','Duplicate')"

After that you can read the value (calculated via formula):
retVal = Sheets(1).Range("C2").Value

Note: retval should be declared as string variable
Now, you can use it for further processing:
If retval = "" Then
    'not duplicate 
Else
    'duplicate 
End If
   
Comments
_duDE 9-Oct-15 9:54am
   
+5 !
Maciej Los 9-Oct-15 10:38am
   
Thank you.
Venkat Raghvan 12-Oct-15 2:46am
   
It is giving an error that 'Application-defined or object-defined error'.What is that? How to solve this?
Maciej Los 12-Oct-15 9:47am
   
What line?
Venkat Raghvan 15-Oct-15 1:35am
   
On formula line

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100