Fin the most frequently Cell in a Row
 

Now hi...

i got a little problem in my VB Script...
i have to find the most frequently cell (string) in a single row:

ex:
( | x | <-- a cell)

| Pete | Pete| Tom | Pete | Joe | Joe | Joe | Pete | Frank |

= Pete 4 times; Tom 2 times; Joe 3 times; Frank 1
function returns: "Pete"

the problem is, that i don't really know how many cells in a row ther
are...
i tried to solve it like this, but this doesn't work out... (i als
know why... and now searching for another solution...)


Private Function Referenzstring() As String

Dim zaehler(1 To 100) As Integer
Dim werte(1 To 100) As String
Dim i As Integer
Dim j As Integer
Dim addcounter As Integer
Dim Count As Integer

werte(1) = Worksheets(1).Cells(CurrentRow, 1).FormulaLocal
addcounter = 1
zaehler(1) = 1

'doesn't workk.. other solution required...
For j = 1 To intSpaltenCount - 1
For i = 1 To intSpaltenCount - j
If StrComp(Worksheets(1).Cells(CurrentRow, i
j).FormulaLocal, werte(j)) Then
zaehler(j) = zaehler(j) + 1
Else
addcounter = addcounter + 1
zaehler(i + j) = zaehler(i + j) + 1
werte(i + j) = Worksheets(1).Cells(CurrentRow, i
j).FormulaLocal
End If
Next i
Next j
#

i = 1
For j = 2 To addcounter
If zaehler(i) < zaehler(j) Then
i = j
End If
Next j

Referenzstring = werte(i)

End Function


--
Message posted from http://www.ExcelForum.com

Fin the most frequently Cell in a Row
 

Go to Chip Pearson's site and look at....
"Most Common String In A Range"
http://www.cpearson.com/excel/excelF.htm
--
XL2002
Regards

William



"Kendor " wrote in message
...
| Now hi...
|
| i got a little problem in my VB Script...
| i have to find the most frequently cell (string) in a single row:
|
| ex:
| ( | x | <-- a cell)
|
| | Pete | Pete| Tom | Pete | Joe | Joe | Joe | Pete | Frank |
|
| = Pete 4 times; Tom 2 times; Joe 3 times; Frank 1
| function returns: "Pete"
|
| the problem is, that i don't really know how many cells in a row there
| are...
| i tried to solve it like this, but this doesn't work out... (i also
| know why... and now searching for another solution...)
|
|
| Private Function Referenzstring() As String
|
| Dim zaehler(1 To 100) As Integer
| Dim werte(1 To 100) As String
| Dim i As Integer
| Dim j As Integer
| Dim addcounter As Integer
| Dim Count As Integer
|
| werte(1) = Worksheets(1).Cells(CurrentRow, 1).FormulaLocal
| addcounter = 1
| zaehler(1) = 1
|
| 'doesn't workk.. other solution required...
| For j = 1 To intSpaltenCount - 1
| For i = 1 To intSpaltenCount - j
| If StrComp(Worksheets(1).Cells(CurrentRow, i +
| j).FormulaLocal, werte(j)) Then
| zaehler(j) = zaehler(j) + 1
| Else
| addcounter = addcounter + 1
| zaehler(i + j) = zaehler(i + j) + 1
| werte(i + j) = Worksheets(1).Cells(CurrentRow, i +
| j).FormulaLocal
| End If
| Next i
| Next j
| #
|
| i = 1
| For j = 2 To addcounter
| If zaehler(i) < zaehler(j) Then
| i = j
| End If
| Next j
|
| Referenzstring = werte(i)
|
| End Function
|
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Fin the most frequently Cell in a Row
 

ok thanks...


now, how to port this to my vba script?

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)

--
Message posted from http://www.ExcelForum.com

Fin the most frequently Cell in a Row
 

i know its not VB, but might it be easier to use a pivot table?

"Kendor " wrote in message
...
Now hi...

i got a little problem in my VB Script...
i have to find the most frequently cell (string) in a single row:

ex:
( | x | <-- a cell)

| Pete | Pete| Tom | Pete | Joe | Joe | Joe | Pete | Frank |

= Pete 4 times; Tom 2 times; Joe 3 times; Frank 1
function returns: "Pete"

the problem is, that i don't really know how many cells in a row there
are...
i tried to solve it like this, but this doesn't work out... (i also
know why... and now searching for another solution...)


Private Function Referenzstring() As String

Dim zaehler(1 To 100) As Integer
Dim werte(1 To 100) As String
Dim i As Integer
Dim j As Integer
Dim addcounter As Integer
Dim Count As Integer

werte(1) = Worksheets(1).Cells(CurrentRow, 1).FormulaLocal
addcounter = 1
zaehler(1) = 1

'doesn't workk.. other solution required...
For j = 1 To intSpaltenCount - 1
For i = 1 To intSpaltenCount - j
If StrComp(Worksheets(1).Cells(CurrentRow, i +
j).FormulaLocal, werte(j)) Then
zaehler(j) = zaehler(j) + 1
Else
addcounter = addcounter + 1
zaehler(i + j) = zaehler(i + j) + 1
werte(i + j) = Worksheets(1).Cells(CurrentRow, i +
j).FormulaLocal
End If
Next i
Next j
#

i = 1
For j = 2 To addcounter
If zaehler(i) < zaehler(j) Then
i = j
End If
Next j

Referenzstring = werte(i)

End Function



---
Message posted from http://www.ExcelForum.com/

Fin the most frequently Cell in a Row
 

any suggestion

--
Message posted from http://www.ExcelForum.com

Fin the most frequently Cell in a Row
 

Well assuming that your range (row) is called rng then something like:
with application.worksheetfunction
Referenzstring =
..INDEX(range("Rng"),.MATCH(.MAX(.COUNTIF(range("R ng"),range("Rng"))),.COUNTI
F(range("Rng"),range("Rng")),0))
end with

This is untested and may need tweeking (particularly the use of the range()
property.


"Kendor " wrote in message
...
ok thanks...


now, how to port this to my vba script?

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))


---
Message posted from http://www.ExcelForum.com/

Fin the most frequently Cell in a Row
 

From Chip's page:
the following **array formula** will return the most frequently used entry
in a range:

Using your approach will not treat the formula as an array formula and will
therefore not work.

You could use Evaluate:

Sub AAABBBCCC()
Dim sStr as String, vVar as variant
sStr = "INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))"
sStr = Application.Substitute(sStr, "Rng", "A1:A200")
vVAr = Evaluate(sStr)
MsgBox vVAr
End Sub



--
Regards,
Tom Ogilvy


"Paul Lautman" wrote in message
...
Well assuming that your range (row) is called rng then something like:
with application.worksheetfunction
Referenzstring =

..INDEX(range("Rng"),.MATCH(.MAX(.COUNTIF(range("R ng"),range("Rng"))),.COUNTI
F(range("Rng"),range("Rng")),0))
end with

This is untested and may need tweeking (particularly the use of the
range()
property.


"Kendor " wrote in message
...
ok thanks...


now, how to port this to my vba script?

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))


---
Message posted from http://www.ExcelForum.com/