Fill cell based on VAT code

[marcidee]

I am looking for help to autofill cells based on VAT codes

In column I are the tax codes which are either T1, T2, T9 or T13

in column J I would like to see against either T2 or T9 - 0
For T13 - no result
For T1 - column H has the gross amount - in column J I require the VAT included (so if column H is £30 the VAT would be £5) I need 5.00 in column J

Then in an ideal world (if it is possible) column H will be changed to 25.00

see below

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]Before
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]H
[/TD]
[TD="class: xl65, bgcolor: transparent"]I
[/TD]
[TD="class: xl65, bgcolor: transparent"]J
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #33CCCC"]Net Amount
[/TD]
[TD="class: xl66, bgcolor: #33CCCC"]Tax Code
[/TD]
[TD="class: xl66, bgcolor: #33CCCC"]Tax Amount
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]30
[/TD]
[TD="class: xl65, bgcolor: transparent"]T1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12.35
[/TD]
[TD="class: xl65, bgcolor: transparent"]T1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]After
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]H
[/TD]
[TD="class: xl65, bgcolor: transparent"]I
[/TD]
[TD="class: xl65, bgcolor: transparent"]J
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #33CCCC"]Net Amount
[/TD]
[TD="class: xl66, bgcolor: #33CCCC"]Tax Code
[/TD]
[TD="class: xl66, bgcolor: #33CCCC"]Tax Amount
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]25
[/TD]
[TD="class: xl65, bgcolor: transparent"]T1
[/TD]
[TD="class: xl65, bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10.29
[/TD]
[TD="class: xl65, bgcolor: transparent"]T1
[/TD]
[TD="bgcolor: transparent, align: right"]2.06
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trevor_S]

You can complete column J using an IF formula - assuming the first row to calculate is row 2:
=IF(I2="T1", ROUND(H2/6,2),IF(OR(I2="T2", I2="T9"), 0,""))

This won't change column H ... and you can't/shouldn't do that, otherwise the column J formula would repeatedly deduct VAT from the new (net) column H figure - until both the column H and J figures were virtually nothing.

I personally would have separate columns for gross and net, so you can leave figures and formulae in place, providing a clear audit trail. However, if you want to overwrite the column H figure and leave column J as a value (so that it doesn't recalculate), I think you need a macro:

Sub CalcVat()
Range("I2").Select
Do
If Selection.Value = "T1" Then
    Selection.Offset(0, 1).Value = Selection.Offset(0, -1).Value / 6
    Selection.Offset(0, -1).Value = Selection.Offset(0, -1).Value - Selection.Offset(0, 1).Value
Else
    If Selection.Value = "T2" Or Selection.Value = "T9" Then
        Selection.Offset(0, 1).Value = 0
    Else
        If Selection.Value = "T13" Then
            Selection.Offset(0, 1).Value = ""
        Else
            Exit Do
        End If
    End If
End If
Selection.Offset(1, 0).Select
Loop
End Sub

 

[marcidee]

Hi Trevor

Thank you so much for this - had a quick try and this seems to work perfectly. I will have a proper look tomorrow afternoon

The calculations are for a template to import data into an accounting passage and it only requires net plus tax amount.

This is so much appreciated, I will revert if I have any problems - thank you again

Marc