New VAT Rate

N

N1KO

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.
 
M

Mike H

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
 
N

N1KO

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Active Page Name 7
historic VAT Rate lookup 10
VAT on Report 5
Variable VAT by product and customer 2
Amount minus VAT 6
Working out VAT owed 1
Validation of UK VAT Number 21
entering VAT rate 2

Top