Update prices automatically on a specific date

jeff2813

New Member
Joined
Jul 17, 2019
Messages
30
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
.
For a single cell :

Code:
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 :

Code:
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 :



A
B
1
ItemPrice
2
Bread
$2.89​
3
Milk
$3.89​
4
Eggs
$1.89​
5
Butter
$1.78​
6
Hamburger
$2.35​
7
Chicken
$2.15​
8
Squash
$0.98​
9
Tomato
$1.29​
10
Banana
$0.57​
 
Upvote 0
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
 
Upvote 0
.
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 ?
 
Upvote 0
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.
 
Upvote 0
.
Along with the other macro ... paste this in the ThisWorkbook module :

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top