View Full Version : Solved: count the cell hits
nandakumar
11-07-2007, 09:12 PM
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
11-07-2007, 10:18 PM
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
11-08-2007, 03:26 PM
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
11-08-2007, 07:09 PM
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
11-09-2007, 08:30 AM
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
11-09-2007, 10:11 AM
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
04-06-2010, 11:14 AM
hello brandtrock,
it is wanderful code thank you herzerg too
Bob Phillips
04-06-2010, 11:52 AM
Took you a long time to realise that!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.