Solved: count the cell hits

[nandakumar]

hi,
anibody know or having macro for count the number of hits for each cell,
example : I have a data in colum A1 TO A100, which has been used by the group, I want to find out how many time each cell been hit,shuld be counted in colum-B..

thnaks in advance. :bug:

[XLGibbs]

Not sure I follow what you mean by "hits".

If you mean "occupied with data" you would use the COUNTA function.

If you meant count where a certain value is present use COUNTIF

[nandakumar]

hi,
thanks for replying, I want to count the how many time cell being clicked by user and has to be specified in a cell. Example: A1 cell is clicked hundred times. So this value has to be appear in any cel as "100"

thanks,

nandakumar

[herzberg]

I think this may be what you are looking for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static CountHit As Long

If Not Intersect(Target, Range("A1")) Is Nothing Then
CountHit = CountHit + 1
End If

Range("B1").Value = CountHit
End Sub

[Brandtrock]

Herzberg's code works fine for the single cell.

Try this for the range you indicated (A1:A100)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + 1

End If


End Sub

[Zack Barresse]

nandakumar, just so you know (I didn't read it anywhere in your posts), the selection change event will fire whenever you newly click a cell. This means that if you select A1 (and it is in the selection change events range specified for your work, etc), if you keep clicking on it the event will not fire. You will get one click, then must navigate away, then click it again (all this to get two "click").

[nandakumar]

hello brandtrock,

it is wanderful code thank you herzerg too

[Bob Phillips]

Took you a long time to realise that!