VBA code to display most frequent text

[JPM]

I have a range D2:D2500 with various names of clients. I am trying to figure out a VBA solution to display which one appears the most time.
On another thread other board contributors helped me with finding various array formula solution. ( http://www.mrexcel.com/forum/showthread.php?p=2641535#post2641535 )

But I am now looking for a VBA solution to display the result in MsgBox.

Any ideas?

 

[JackDanIce]

This is sort of using suggestions in the link you provided - i.e. not a 'clean' VBA method, but try:

Sub ModalClientName ()
Dim Mode_Client as String
Range("A" & Rows.Count).FormulaArray = _
"=INDEX(D2:D2500,MODE(MATCH(D2:D2500,D2:D2500,0)))"
Mode_Client = Range("A" & Rows.Count)
Range("A" & Rows.Count).ClearContents
MsgBox "The client occurring the most is: " & Mode_Client
End Sub

Hopefully someone else can improve this without passing the result of the formula into a cell, but directly into the variable that can be called up with the msgbox

 

[JPM]

I like your approach, incorporating the array formula, however, something isn't working. I get Range("A" & Rows.Count) = Error 2042 which doesn't mean anything to me.

 

[JPM]

JackDanIce, thanks for your code. The problem was with the formula. I switched it out for =INDEX(D:D, 10000*MOD(MAX(COUNTIF(D1:D2545,D1:D2545)+(ROW(D1:D2545)/10000)),1), 1)
and it worked just fine.

Thanks.

 

[JPM]

Is there another way to maybe incorporate the formula in the code directly like using the WorksheetFunction command?

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG10Mar45
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] oMax [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] Msg [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("D2"), Range("D" & rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Not .Exists(Dn.value) [COLOR=navy]Then[/COLOR]
                .Add Dn.value, 1
            [COLOR=navy]Else[/COLOR]
                .Item(Dn.value) = .Item(Dn.value) + 1
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR]
oMax = Application.Max(Application.Transpose(.Items))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
    [COLOR=navy]If[/COLOR] .Item(K) = oMax [COLOR=navy]Then[/COLOR]
        Msg = Msg & K & ","
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] K
MsgBox "The Number/s :- " & Msg & " Appeared " & oMax & " Times"
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[JPM]

Thanks MickG, that works. It's a lot of code though. I liked the idea of using the array formula as the code seems shorter - if I could do it all in VBA and not paste it into the worksheet.

 

[Domenic]

The following code assigns the mode to a variable...

MyVar = Application.Evaluate("INDEX(D2:D2500,MODE(IF(D2:D2500<>"""",MATCH(D2:D2500,D2:D2500,0))))")

 

[JPM]

Thanks everyone, for your help. I think I got now what I was looking for. MickG, I see the value in your code, as it is not limited to a set range, but goes until the end.

 

[Domenic]

For a dynamic range, try...

LastRow = Cells(Rows.Count, "D").End(xlUp).Row

MyVar = Application.Evaluate("INDEX(D2:D" & LastRow & ",MODE(IF(D2:D" & LastRow & "<>"""",MATCH(D2:D" & LastRow & ",D2:D" & LastRow & ",0))))")