Help required to count data in a range -

[chippymark]

Hi, I have a cell range set-up as a calender format in which names will be entered randomly. I want to have some sort of function so if I enter for example Fred1 in 7 different cells that another cell for example ("W1") will show Fred1 = 7
If I then went on to enter another name lets say Fred2 and have 5 entries could ("W2") show Fred2=5
I know I can use the countif function but I only want to show results if a name has been entered.

Any help advise will be much appreciated,
Cheers
Mark

 

[Richard Schollar]

Is there some rule eg specifying which cell will contain the first entry eg if it is always in the A column you could do this:

=IF(A1<>"",COUNTIF(A1:V1,A1),"")

 

[MickG]

Perhaps:-

Function MyNam(rng As Range) As String
 If rng(1) <> "" Then
    MyNam = rng(1) & " = " & Application.CountIf(rng, rng(1))
 End If
End Function

 

[chippymark]

Is there some rule eg specifying which cell will contain the first entry eg if it is always in the A column you could do this:

=IF(A1<>"",COUNTIF(A1:V1,A1),"")

Unfortunately the data can appear anywhere within the range. and can pretty one of any 200 names.
There might only be 1 entry in the month or there could be 66 different names entered.

 

[chippymark]

Perhaps:-

Function MyNam(rng As Range) As String
 If rng(1) <> "" Then
    MyNam = rng(1) & " = " & Application.CountIf(rng, rng(1))
 End If
End Function

Hi, How would I assign this code, lets say the range in which the names could fall is B4:AL20
So I assign the code above to a button, please bear in mind I am still very much a novice on Macro's etc.. But I am getting there )

 

[chippymark]

Can anybody help ? Am I making sense ?
anwhere within the range A2:W30 random names can be entered I need to be able to show the results in a different column.

See My example below

A...........B...........C...........D.........E >>>>W
1
2 FRED1...............PAUL
3
4............FRED1................FRED1
5 PAUL

So lets say Column 'X' displays the results:
X
1 FRED1 = 3
2 PAUL = 2

Any Ideas ?

 

[MickG]

Try this:-
Right click sheet tab.
Select "View Code", vb window appears.
Paste code into window.
Close VB Window.
Click Any Cell name in range, as Set in code.(alter range to suit)
Msgbox appears with result and also in "W1" , ALter code to suit as shown.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Set Rng = Range("B4:AL20") '[COLOR="Green"][B]Alter range to suit[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Not Intersect(Rng, Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    Nam = Target & " = " & Application.CountIf(Rng, Target)
        [COLOR="Navy"]If[/COLOR] Not Target = vbNullString [COLOR="Navy"]Then[/COLOR]
            MsgBox Nam '[COLOR="Green"][B]Remove as required[/B][/COLOR]
            Range("W1") = Nam '[COLOR="Green"][B]Remove as required[/B][/COLOR]
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[chippymark]

Try this:-
Right click sheet tab.
Select "View Code", vb window appears.
Paste code into window.
Close VB Window.
Click Any Cell name in range, as Set in code.(alter range to suit)
Msgbox appears with result and also in "W1" , ALter code to suit as shown.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Set Rng = Range("B4:AL20") '[COLOR="Green"][B]Alter range to suit[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Not Intersect(Rng, Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    Nam = Target & " = " & Application.CountIf(Rng, Target)
        [COLOR="Navy"]If[/COLOR] Not Target = vbNullString [COLOR="Navy"]Then[/COLOR]
            MsgBox Nam '[COLOR="Green"][B]Remove as required[/B][/COLOR]
            Range("W1") = Nam '[COLOR="Green"][B]Remove as required[/B][/COLOR]
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick[/QUOTE

That's excellent Mick, was originally looking for all the results to be posted to a column but your solution is very handy and will work for the time being.

Regards

Mark