A way to update everytime a cell is updated (based on days)

vimsikalbadtard

New Member
Joined
Oct 22, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hello... so i have this excel database with me which shows the discount given on all the products in the market. Initially the discount of lets say product x is -12%... It did not sell.. 15 days later i change it to -18%... it still didn't sell... and i keep changing and giving a higher discount till its sold.
My request here is that is there a way to find out how many times the particular cell has been updated? preferably in days.. for eg: product x was updated 10 times before it was sold so the cell should say 10. But, the count will go up only once in one day regardless of you changing it 8 times in a day.. final count will be 1 for that day.I was able to come up with this code however it updated it every time i inserted a new value and that would lead to 10-12 times a day instead of 1.

VBA Code:
Dim xCount As Integer
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    If Target = Range("B:B") Then
        xCount = xCount + 1
        Range("C:C").Value = xCount                                   
    End If
    Application.EnableEvents = False
    Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B"))
    If Not xRg Is Nothing Then
        xCount = xCount + 1
        Range("C:C").Value = xCount
    End If
    Application.EnableEvents = True
End Sub
[/CODE]

The above code was given by a friend. I dont know much about it.
I know this is a really hard request but please help me out. (Im not that good with VBA, sorry in advance)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,397
Office Version
  1. 365
Platform
  1. Windows
The one with the changing discounts
You may know which one that it, but I don't.

Although your description referred to a "particular cell" perhaps you actually meant any cell in the column?
If so, is that column B with the changing discounts?
 

vimsikalbadtard

New Member
Joined
Oct 22, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You may know which one that it, but I don't.

Although your description referred to a "particular cell" perhaps you actually meant any cell in the column?
If so, is that column B with the changing discounts?
extremely sorry for the confusion. Yes it will be colB.. Particular cell was an example. The whole columnB will be having changes in prices and maybe colC will show how many changes made daywise?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,397
Office Version
  1. 365
Platform
  1. Windows
Try this Worksheet change code instead of the above. It uses column Z to record what date the column B cell in that row was last changed so that you don't get multiple increments on the same day.
That can be any column you want and could be hidden.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Const DateCol As String = "Z"
  
  Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If Not IsEmpty(.Value) And Range(DateCol & .Row).Value <> Date Then
          .Offset(, 1).Value = .Offset(, 1).Value + 1
          Range(DateCol & .Row).Value = Date
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub
 
Solution

vimsikalbadtard

New Member
Joined
Oct 22, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Try this Worksheet change code instead of the above. It uses column Z to record what date the column B cell in that row was last changed so that you don't get multiple increments on the same day.
That can be any column you want and could be hidden.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Const DateCol As String = "Z"
 
  Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If Not IsEmpty(.Value) And Range(DateCol & .Row).Value <> Date Then
          .Offset(, 1).Value = .Offset(, 1).Value + 1
          Range(DateCol & .Row).Value = Date
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub
This works perfectly... Thank you so much! hope you have a wonderful day :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,397
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,311
Messages
5,836,583
Members
430,438
Latest member
David Gr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top