Using a defined range within an index formula

[Blunder1]

Hi,

In my code i need to count the mose frequent names in a list. If i set the range as 'rng' for teh column how do i use this in the formule?

This is what i have as an using FormulaArray:-

<TABLE style="WIDTH: 181pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=241 border=0 x:str><COLGROUP><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 181pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=241 height=17 x:str="'=INDEX(Stats!H1:H847,MATCH(MAX(COUNTIF(Stats!H2:H618,Stats!H2:H618)),COUNTIF(Stats!H2:H618,Stats!H2:H618),0))">

=INDEX(Stats!H1:H847,MATCH(MAX(COUNTIF(Stats!H2:H618,Stats!H2:H618)),COUNTIF(Stats!H2:H618,Stats!H2:H618),0))

What i would like is:-

=index(rng,match(max(countif(rng,rng)),countif(rng,rng),0))

</TD></TR></TBODY></TABLE>

 

[RoryA]

It's not clear to me what your actual problem is? Have you named the range?

 

[Blunder1]

Hi,
I want to replace using 'Stats!H2:H618' and use 'rng' instead. Rng has been set.

Thanks

 

[RoryA]

And what is the problem you are having when you do that?

 

[Blunder1]

the cell in the worksheet is populated with rng and does not know what this is. I think the formula needs to be worked out within the vba code, such as using 'worksheetfunction'?

 

[RoryA]

You haven't mentioned VBA at all up until now - I assumed you had named the range 'rng' in which case the formula would work as is.

 

[Blunder1]

apologies, i missed out the most vital part!! Can you help on this?

thanks

 

[RoryA]

Well, your question doesn't really make sense now, so I'd have to guess you mean something like:

dim strAddy as String
strAddy = rng.address
Activecell.formula = "=index(" & straddy & ",match(max(countif(" & straddy & "," & straddy & ")),countif(" & straddy & "," & straddy & "),0))"

 

[Blunder1]

Thank you, that is perfect. And sorry for not explaing the question well

 

[RoryA]

Not to worry - at least we got there in the end.