Hi All,

This is probably easy but.

Due to the possibility that the VAT rate may change from 17.5% to 15% i need
to change a lot of prices.

All the prices in my sheet are inclusive of VAT, I need to find the VAT
amount, then find the price without VAT and then find the price of adding the
new VAT amount on to it.

I know how to do this with simple formulas on the sheet but i need to create
some vba code so i can run it in various workbooks.

Currently i have this in the cells.

A1 - £49.00 - price
B1 - =A1*7/47 - to get the VAT amount
C1 - =A1-B1 - price without VAT
D1 - =C1+15%

I can do the loops and stuff to run it down the sheet i just need the code
to allow all these details to be automatically entered as it goes down the
rows.

If anyone has any ideas about how i could find this that would be appreciated.

Hi,

What your addressing (apart from government madness) is a classic error in
spreadsheet design. Things like VAT should be held in a single cell and
referrred to in a formula. that way when it changes you can change a single
cell and your done. However you are where you are. This refers to the 'new'
vat rate in cell M1 which you must enter and can change to suit.

Right click your sheet tab, view code and paste the code below in and run
it. Once you've run it change m1 back to 17.5% and you'll see what I mean
about a single cell altering all your vat calculations,

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
c.Offset(, 1).Formula = "=" & c.Address & "*7/47"
c.Offset(, 2).Formula = "=" & c.Address & "-" & c.Offset(, 1).Address
c.Offset(, 3).Formula = "=" & c.Offset(, 2).Address _
& "+" & c.Offset(, 2).Address & "*" & Range("$M$1").Address
Next
End Sub

Mike

"N1KO" wrote:

Hi All,

This is probably easy but.

Due to the possibility that the VAT rate may change from 17.5% to 15% i need
to change a lot of prices.

All the prices in my sheet are inclusive of VAT, I need to find the VAT
amount, then find the price without VAT and then find the price of adding the
new VAT amount on to it.

I know how to do this with simple formulas on the sheet but i need to create
some vba code so i can run it in various workbooks.

Currently i have this in the cells.

A1 - £49.00 - price
B1 - =A1*7/47 - to get the VAT amount
C1 - =A1-B1 - price without VAT
D1 - =C1+15%

I can do the loops and stuff to run it down the sheet i just need the code
to allow all these details to be automatically entered as it goes down the
rows.

If anyone has any ideas about how i could find this that would be appreciated.

You could multiply the total price by 0.978724. To do it in one action
use paste special


'Excel PasteSpecial' (http://www.excel-it.com/excel_pastespecial.htm)


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site
' (http://www.excel-it.com)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33273

Hi Mike,

Unfortunately we get our retail prices from a Telnet Report which runs from
BOS, we don't have control over this so i'm having to do it this way.........

Thanks for your answer mind, i'll give it a try.

"Mike H" wrote:

Hi,

What your addressing (apart from government madness) is a classic error in
spreadsheet design. Things like VAT should be held in a single cell and
referrred to in a formula. that way when it changes you can change a single
cell and your done. However you are where you are. This refers to the 'new'
vat rate in cell M1 which you must enter and can change to suit.

Right click your sheet tab, view code and paste the code below in and run
it. Once you've run it change m1 back to 17.5% and you'll see what I mean
about a single cell altering all your vat calculations,

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
c.Offset(, 1).Formula = "=" & c.Address & "*7/47"
c.Offset(, 2).Formula = "=" & c.Address & "-" & c.Offset(, 1).Address
c.Offset(, 3).Formula = "=" & c.Offset(, 2).Address _
& "+" & c.Offset(, 2).Address & "*" & Range("$M$1").Address
Next
End Sub

Mike

"N1KO" wrote:

Hi All,

This is probably easy but.

Due to the possibility that the VAT rate may change from 17.5% to 15% i need
to change a lot of prices.

All the prices in my sheet are inclusive of VAT, I need to find the VAT
amount, then find the price without VAT and then find the price of adding the
new VAT amount on to it.

I know how to do this with simple formulas on the sheet but i need to create
some vba code so i can run it in various workbooks.

Currently i have this in the cells.

A1 - £49.00 - price
B1 - =A1*7/47 - to get the VAT amount
C1 - =A1-B1 - price without VAT
D1 - =C1+15%

I can do the loops and stuff to run it down the sheet i just need the code
to allow all these details to be automatically entered as it goes down the
rows.

If anyone has any ideas about how i could find this that would be appreciated.