Rewriting Formula to VBA

[jarvisaurus]

I'm having some trouble rewriting this vba formula, some assistance would be greatly appreciated. Thanks.

Current excel formula:

=--ISNUMBER(MATCH(AP2,$AP$1:AP1,0))

VBA:

Cells(i, "AS").Value = Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Match(Cells(i, "AP"), Range("$AP$1:AP1"), 0))

 

[Domenic]

Try...

[font=Verdana]Cells(i, "AS").Value = Application.Evaluate("ISNUMBER(MATCH(AP" & i & ",$AP$1:AP1,0))+0")[/font]

Although, unless I'm mistaken, it looks like the range for the lookup array, $AP$1:AP1, should be expanding with each iteration.

 

[jarvisaurus]

Sorry about that, yes it should be expanding with each iteration. How would I adjust the code?

 

[jarvisaurus]

Nevermind I figured it out but thank you!

Cells(i, "AS").Value = Application.Evaluate("ISNUMBER(MATCH(AP" & i & ",AP" & i - 1 & ",0))+0")

 

[Domenic]

Shouldn't it be...?

Cells(i, "AS").Value = Application.Evaluate("ISNUMBER(MATCH(AP" & i & ",AP1:AP" & i - 1 & ",0))+0")

 

[jarvisaurus]

Yes! Thank you so much...I was wondering why I wasn't picking up any duplicates lol