How to detect amount of changes in cell?‏

[defy]

Hi All,

I need a macro which quietly counts changes made to a cell and stores them even after the workbook is closed.
If the cell has been modified more then say 5 times in one day it is highlighted yellow more then 10 highlighted red. Then i need week counts and month counts (years maybe later), hence the ability to store and add amount of changes.

Any ideas? I so far got this much:

Code:


Static AncAdress As String, AncCell As Variant
If AncAdress <> "" Then 'for first initialization.
If AncCell <> Range(AncAdress) Then

'this detectes that the sell has been changed so i need something here to count amount of changes to which cell)

Stop
End If
End If
AncAdress = Target.Address
AncCell = Target.Value2
End Sub

I was thinking of creating a new list called COUNTING. Say in my DATA list i changed the value of cell A2 from bob to 2. So in my COUNTING list A2 will have a value "today = 1". Hence if i decide to change its value later on in the month it will be "month = 2". also if it is shifted in DATA i.e. moved to a different location then it need to be tracked and moved in COUNTING (basically value mapping).
has any

So any ideas? I'm also open to suggestions if any ^^

 

[Rastaman]

Hi defy, welcome to the board!

Here's one option that might work for you. I created a new sheet called Counters, shown below. I then added a worksheet change event macro that runs whenever cell A2 of Sheet1 is changed. The macro looks at the current day/week/month/year compared to the last time the cell was changed. If the same, the counter gets increased by 1. If not the same, the counter is reset to 1. You can hide (with xlveryhidden if desired) the Counters sheet. As far as cell color, this can be done with conditional formatting.

Still, your post left me with several questions. What color do you want when the day count is less than 5 but the week count is a high number? What color should the cell be when the file is opened for the first time in a day? Do you want to save the prior day/week/month/year counts?

Here's my Counters sheet:

Excel Workbook
	A	B	C	D	E	F
1	Event	Current	Counting	Count	current value	last change
2	Day	12/18/2010	12/18/2010	16	10	12/18/2010 23:19
3	Week	51	51	19
4	Month	12	12	15
5	Year	2010	2010	9
Counters

Code in Sheet1 or the sheet that has the monitored cell. Modify the target address as necessary:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$A$2" Then

Dim CountSht As Worksheet
Set CountSht = Worksheets("Counters")
        
    'check if cell actually changed
    If CountSht.Range("E2") = Range("A2") Then
        Exit Sub
    Else
        CountSht.Range("E2") = Range("A2")
        CountSht.Range("F2") = Now()
    End If
    
    'count day events
    If CountSht.Range("C2") = CountSht.Range("B2") Then
        CountSht.Range("D2") = CountSht.Range("D2") + 1
    Else
        CountSht.Range("C2") = CountSht.Range("B2")
        CountSht.Range("D2") = 1
    End If
    
    'count week events
    If CountSht.Range("C3") = CountSht.Range("B3") Then
        CountSht.Range("D3") = CountSht.Range("D3") + 1
    Else
        CountSht.Range("C3") = CountSht.Range("B3")
        CountSht.Range("D3") = 1
    End If
    
    'count month events
    If CountSht.Range("C4") = CountSht.Range("B4") Then
        CountSht.Range("D4") = CountSht.Range("D4") + 1
    Else
        CountSht.Range("C4") = CountSht.Range("B4")
        CountSht.Range("D4") = 1
    End If
    
    'count year events
    If CountSht.Range("C5") = CountSht.Range("B5") Then
        CountSht.Range("D5") = CountSht.Range("D5") + 1
    Else
        CountSht.Range("C5") = CountSht.Range("B5")
        CountSht.Range("D5") = 1
    End If

End If

End Sub

 

[defy]

Sorry mate, i must have confused you very badly.. still your code is giving me some food for thought. (Thank you!)

Ill give you a visioal example of what im trying to do

Say i have 100 pens, 200 pencils, 300 rulers...

They all get taken on a daily basis, I want to create a data sheet that if for example my outflow of pens is very high per day, then i want it to be highlighted.

Also i want see how much material is being given out per week, per month so i would know how much of what i need to purchase to keep my stock on a safe level i.e. i wont be runing out pf materials all of a sudden

So i need to check how much of that particular material is being withdrawn + if it is being whithdrawn in batches i.e. is there a pen with drawn once a day 5 pens once a day etc., i hope this clarifies it slightly?

Let me know if you need any more info...

Again i just came with this today so it all up in the air

P.S. its a change tracking system

 

[Rastaman]

Yes, that is a bit different. You mention counting changes made to a cell. What is in the cell to start with and what is the change made to it?

 

[defy]

To start with there are numbers, but the location of the cell is unknown. it is detected by change event.

In a1 we have pencil in b1 we have 100. Some one requested (outflow) 10 pencils.
b1 is now changed to 90.

Tracking list should say in b1 " 20-dec-2010: 5"
Then lets say we have another list called tracking over all so:
A1: December 2010
A2: pencil b2: 10
A3: pen b2: 0
etc.

At the end the will be:
Axx: Total for 2010
Axx: pencil Bxx:5
etc

the 2 lists should automatically update with each with drawl, and go into 2011 and 2012 on auto.

Also if there was a material return or purchase (inflow), so some one put 25 pencils in. that is to be recorded separately. This can be done by subtracting the the new value from previous value and checking if it negative. if -ve then update the inflow sheet in similar manner.

This is kinda growing slowly as i get new ideas coming. There more i think about this code the more problems I'm seeing :D
The inflow list i came up with just as i was typing this

Let me know if i can clarify it any more :P

 

[Rastaman]

Ok, it sounds like you need to figure out what information you want to collect and how you will use it. Then you can design an input and reporting screen. I did a quick Google search for Inventory Management on this board, there are many examples, including ideas for using a form as the input for the user. I'd suggest browsing through these for some ideas.

Regarding your post, you say that the location of the cell is unknown. I'm guessing you mean the input cell where the current inventory is stored, but then you describe how the user changes cell A2. Below is what I think you are describing, but I don't know if this output gives you the ability to analyze consumption in the way you describe.

Input sheet:

Excel Workbook
	A	B
1	Pencils	250
2	Pens	125
3	Rulers	100
Sheet1

Report sheet:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Pencils
2	Day	Qty	Week	Qty	Month	Qty	Year	Qty
3	11/28/2010	10	11/28/2010	51	Nov-10	32	2010	101
4	11/30/2010	22	12/5/2010	40	Dec-10	69
5	12/1/2010	15	12/12/2010	10
6	12/2/2010	4
7	12/5/2010	30
8	12/6/2010	2
9	12/10/2010	8
10	12/15/2010	10
Sheet2