PDA

View Full Version : [SOLVED] Worksheet not updating with data from VBA



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

SamT
03-28-2014, 03:13 PM
Changes("A:A") are date-Times, accurate to the millisecond. (=Now)

Here's what I suspect

When K2 is only a date (6/04/14), Excel ignores the time value of ("A:A")

When the value in K2 is entered by VBA, it is enter as a DateTime value, (accurate to a millisecond.)

If you don't need to know the time a change was made, merely change "Now" in your code to "Date."

Otherwise, you might try converting the Code entered datetime to a date only with
K2 = DateTimeVar \ 1 '(backslash division)

OR you can try this formula

=SUMPRODUCT((($A$2:$A$900\1)=K2\1)*1)

rollis13
03-28-2014, 03:21 PM
The formula could also be (with or without changing Now to Date in the macro) since you only need the date precision:

=SUMPRODUCT((INT($A$2:$A$900)=K2)*1)

rikapple
03-29-2014, 01:25 AM
The formula could also be (with or without changing Now to Date in the macro) since you only need the date precision:

=SUMPRODUCT((INT($A$2:$A$900)=K2)*1)

Thank you this worked wonderfully! I get understand Excel one day!

Thank you again

rollis13
03-29-2014, 02:59 AM
Glad being of some help :).