Convert a Max(CountIFS()) function to VBA

[MarvinP]

Hi,

I worked for about an hour looking for the correct VBA syntax to do the yellow cell in the attached. Is it possible to do a VBA function to give the maximum number matches between two sets of numbers? In Excel normal functions it is a #Spill so perhaps VBA won't do it??

The Excel Function is:

Please Login or Register  to view this content.

VBA for Max(CountIFS()).xlsm

[Marc L]

See first the Evaluate VBA inner help, a must read.

So in VBA it's the same than under Excel : Debug.Print Sheet1.[MAX(COUNTIFS(B1:K1,B2:K2))] …

[PCI]

Perhaps next function could help
It gives the same results as the Excel formula, but it is only counting macthings from Set1 vs Set2 like the formula, is it what you need ?
If not the function can be updated

Please Login or Register  to view this content.

[PCI]

A remake to take care about previous comment.
Chose the one you want

Please Login or Register  to view this content.

[MarvinP]

Hi PCI,

On row 75 I have a problem. Both your codes above give 1 but doing a Find Dups using CF there are two numbers that are duplicates between these two rows. Am I missing something? See the attached.

Should be 2 matches not 1.xlsm

[PCI]

The formula gives also 1, it is counting the nb of times a number is duplicated, here once, not the amount of duplications.

[Phuocam]

Maybe

Please Login or Register  to view this content.

[protonLeah]

If a number occurs multiple times in set 1, then each match in set 2 is counted. E.g . two occurrences in set 1 and two in set 2 will give a count of 4...

Please Login or Register  to view this content.