Update prices automatically on a specific date

[jeff2813]

Just wondering if its possible to update numbers by inflation on specific dates. As an example, i'd like a loaf of bread at $1 to automatically adust for a predetermined inflation rate on every Jan 1. I know i can make columns but I'd like only one specific cell to constantly update annually. Thanks

 

[Logit]

.
For a single cell :

Option Explicit


Sub autoInflate()
Dim c As Range


    Set c = Sheet1.Range("B2")
        
    c.Value = c.Value + (c.Value * 0.025)
        
            
End Sub

The above will raise the price located in B2 by 2.5%


And if you wanted to raise the price for an entire column :

Option Explicit


Sub autoInflate()
Dim c As Range
Dim IR As Range


    Set IR = Sheet1.Range("B2:B10")
        For Each c In IR
            c.Value = c.Value + (c.Value * 0.025)
        Next c
            
End Sub

The example used is :



[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Item[/td][td]Price[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Bread[/td][td]

$2.89​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]Milk[/td][td]

$3.89​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]Eggs[/td][td]

$1.89​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]Butter[/td][td]

$1.78​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]Hamburger[/td][td]

$2.35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]Chicken[/td][td]

$2.15​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]Squash[/td][td]

$0.98​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]Tomato[/td][td]

$1.29​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]Banana[/td][td]

$0.57​

[/td][/tr]
[/table]

 

[jeff2813]

Thank you for your response. Im trying to do a home inventory and would like they monetary value of an item to change after every year by a prescribed rate forever if need be. Will this change automatically on which ever date i choose? Thanks

 

[Logit]

.
No.

But with just a few more lines of code you can have it change automatically. That's a bit of overkill though when all the user needs to do
is click on the button on the given date.

Is this homework you are obtaining answers for ?

 

[jeff2813]

Nope. Just a newbie. Just wondering if you could have a loaf of bread on july 17, 2019 cost $1 then on july 18, 2020 the same cell would auto recalculate to $1.10. Assuming 10 percent inflation. I may need a course or two or i could be over my head. But thanks.

 

[Logit]

.
Along with the other macro ... paste this in the ThisWorkbook module :

Option Explicit


Private Sub Workbook_Open()
Dim dt As Date, firstDay As Date


    dt = Date
    firstDay = DateSerial(Year(dt), 1, 1)
    
    If Date = firstDay Then
        autoInflate
    End If
   
End Sub

You can change the date on your computer to verify the macro works.

 

[jeff2813]

Ok. I try that tomorrow. Thank you