rikapple
03-28-2014, 01:30 PM
I have the following code which is counting the number of changes made within a workbook.
HTML Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Changes" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = oldVal
Sheets("Changes").Range("E" & lr) = NewVal
Target = NewVal
Application.EnableEvents = True
End Sub
For this I am wanting to capture the number of occurrences of each date.
So if there was 13 occurrences of 6/04/14 I would like a cell count of 13.
I have the following formula within the "changes" worksheet.
HTML Code:
=SUMPRODUCT(($A$2:$A$900=K2)*1)[
the range a2:a900 is where the above VBA is placing the dates.
K2 is the date ie 6/04/14 (there are 6 months worth of days) k3,k4k5 etc
The problem have is that the formula works,but only if I type the date in manually. If the data is pulled through via VBA the formula remains 0.
Not sure were I have gone wrong?
Thanks in advance
HTML Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Changes" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = oldVal
Sheets("Changes").Range("E" & lr) = NewVal
Target = NewVal
Application.EnableEvents = True
End Sub
For this I am wanting to capture the number of occurrences of each date.
So if there was 13 occurrences of 6/04/14 I would like a cell count of 13.
I have the following formula within the "changes" worksheet.
HTML Code:
=SUMPRODUCT(($A$2:$A$900=K2)*1)[
the range a2:a900 is where the above VBA is placing the dates.
K2 is the date ie 6/04/14 (there are 6 months worth of days) k3,k4k5 etc
The problem have is that the formula works,but only if I type the date in manually. If the data is pulled through via VBA the formula remains 0.
Not sure were I have gone wrong?
Thanks in advance