Updating Price List Formula -

[Dosneau]

O.k. Maybe this is easy, but I am having a hard time figuring this out. I have a price list...it has 100 items. Therefore 100 cells with a dollar amount. I have to update this price list by a SET amount maybe 6 times a year. I want to put a formula where the user can enter the SET amount i.e. 3% or .03 and have that calculate the new price. So basically the cell would have the following...

a1 = $6.00
Price Increase = 3%
a1 = (a1*.03)+a1

So now you see my dillema -

1 - Circular reference which I can change via excel options, but then will change the dollar amount as well.
2 - I want it to change every time I put a SET amount in another cell...

Hope the image helps!!!...thanks in advance for the assistance!!!

[/url][/IMG]

 

[shg]

You can do this by copying a cell with the value 1.03, then selecting the price cells, and then paste special > multiply, values.

If you do that, though, you'll end up, eventually, with long decimals.

Instead, you could use an additional column with the formula =round(A1*1.03, 2) and copy that down. Then copy that column and paste as values over the original prices.

 

[Nalani]

Here's a Macro approach:

Sub PercentInc()
    Dim myArea As Range
    Dim myInc As Double
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    If Range("D1").Value = False Then Exit Sub
    myInc = 1 + Range("D1").Value  '//change D1 to where you want to input change value
    For Each myArea In Range("A1:A" & LR).Areas '//Change A1 if you have header
        myArea.Value = Evaluate(myArea.Address & "*" & myInc)
    Next
End Sub

Before macro:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 53px"><COL style="WIDTH: 28px"><COL style="WIDTH: 64px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">$6.00</TD><TD></TD><TD>Increase</TD><TD style="TEXT-ALIGN: right">3.00%</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">$16.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">$2.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">$37.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">$21.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">$10.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">$50.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">$8.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">$5.00</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">$3.00</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


After Macro:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 53px"><COL style="WIDTH: 28px"><COL style="WIDTH: 64px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">$6.18</TD><TD></TD><TD>Increase</TD><TD style="TEXT-ALIGN: right">3.00%</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">$16.48</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">$2.06</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">$38.11</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">$21.63</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">$10.30</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">$51.50</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">$8.24</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">$5.15</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">$3.09</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


Insert code in a Standard module and run from your Macro List. Or you could assign a button to it.

 

[Dosneau]

Thanks GUYS!...NALANI..you rock...this might actually work...going to try it...but one thing...the macro cant be stopped...i.e. if user keeps clicking button which I made assigned a macro to it...it keeps equating... thats the only thing...but other than that I think this will work...

THanks!

 

[Dosneau]

Thanks Nalani,
I have attached the image of the file. I couldnt figure out the macro with headers...otherwise changing D1 was easy. Thought if you could assist...great macro though!...again...any way to stop user from clicking twice...so that it does not keep calculating... THANKS..

So the numbers that are changing is in column H, I & J.

Thanks!

http://www.flickr.com/photos/29859111@N08/5805851009/

 

[Nalani]

Try this:

Sub PercentInc2()
    Dim myArea As Range
    Dim myInc As Double
    If Range("L4").Value = False Then Exit Sub
    myInc = 1 + Range("L4").Value
    For Each myArea In Range("H4:J7, H12:J23").Areas
        myArea.Value = Evaluate(myArea.Address & "*" & myInc)
    Next
End Sub

Based off of your screen shot for H4:J7 and H12:J23

 

[Nalani]

For running your macro once, take a look at these posts and see if you can adapt.

http://www.mrexcel.com/forum/showthread.php?t=357648

http://www.mrexcel.com/forum/showthread.php?t=369913

Found through Search Search MrExcel through Google Here

It also depends on how you inserted the Button - from the ActiveX or Forms tool box.

See where you get with it, and post back if you need help.