Removing vat from a cell automatically

adad67

New Member
Joined
Apr 23, 2019
Messages
10
Hi all,
I am looking to be able to input a figure into a cell and when I press enter, have the cell automatically remove the vat @ 20% I know the calculation is to divide by 1.2, but thats as far as my knowledge takes me!
Is there any code I could use to do this. I have a spreadsheet from work I have to submit each month, so don't really want another column.
It would save me so much time if this was possible... and help would be so appreciated.
Thanks
David
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
David

If you put this in a worksheet module any, numeric, value you enter in A1:A10 will have the VAT removed.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim dblVat As Double

    dblVat = 0.2
    
    If Intersect(Target, Range("A1:A10")) Is Nothing Then
        Exit Sub
    End If
    
    Application.EnableEvents = False
        
    For Each cl In Target.Cells
        If IsNumeric(cl.Value) And cl.Value <> "" Then
            cl.Value = cl.Value / (1 + dblVat)
        End If
    Next cl
    
    Application.EnableEvents = True
    
End Sub
 
Upvote 0
David

If you put this in a worksheet module any, numeric, value you enter in A1:A10 will have the VAT removed.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim dblVat As Double

    dblVat = 0.2
    
    If Intersect(Target, Range("A1:A10")) Is Nothing Then
        Exit Sub
    End If
    
    Application.EnableEvents = False
        
    For Each cl In Target.Cells
        If IsNumeric(cl.Value) And cl.Value <> "" Then
            cl.Value = cl.Value / (1 + dblVat)
        End If
    Next cl
    
    Application.EnableEvents = True
    
End Sub

Thankyou for your speedy reply... I have tried and get the message compile error:Syntax error and the line below is highlighted red
Application.ScreenUpdating = TruePrivate Sub Worksheet_Change(ByVal Target As Range)
Thanks
David
 
Upvote 0
David

Do you have any other code in the worksheet module you tried to put this code in?
 
Upvote 0
David

Do you have any other code in the worksheet module you tried to put this code in?

I already have the following in
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim company As Range
Set company = Sheets("Data").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
If Not company Is Nothing Then
Target.Offset(0, 2) = company.Offset(0, 1)
Else
MsgBox (Target & " not found.")
End If
Application.ScreenUpdating = True
End Sub

Thanks
David
 
Upvote 0
Right, the code I posted will need to be added to that code.

For which range do you want to be able to remove the VAT from values when they are entered?

PS What is that existing code meant to do?

Is it returning something from the 'Data' sheet when you enter a company name in column A?
 
Upvote 0
Right, the code I posted will need to be added to that code.

For which range do you want to be able to remove the VAT from values when they are entered?

PS What is that existing code meant to do?

Is it returning something from the 'Data' sheet when you enter a company name in column A?
Thank you, I need everything in column F to minus the vat.
The other code when I enter a contractors name in column a, it automatically fills in colum c to show what the contractor does.. so for eg, if I put Mc Donalds in a... C would say fast food etc etc
David
 
Upvote 0
David

This is rough but give it a try.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim company As Range
Dim dblVat As Double
Dim Res As Variant

    dblVat = 0.2
    
    If Target.Column = 1 Or Target.Column = 5 Then
    
        Application.EnableEvents = False
        
        Select Case Target.Column
            Case 1
                Res = Application.Match(Target.Value, Sheets("Data").Columns(1), 0)
        
                If Not IsError(Res) Then
                    Target.Offset(, 2).Value = Sheets("Data").Range("B" & Res).Value
                Else
                    MsgBox (Target & " not found.")
                End If
            Case 5
                If IsNumeric(Target.Value) And Target.Value <> "" Then
                    Target.Value = Target.Value / (1 + dblVat)
                End If
        End Select
        
        Application.EnableEvents = True
        
    End If
    
End Sub
 
Upvote 0
David

This is rough but give it a try.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim company As Range
Dim dblVat As Double
Dim Res As Variant

    dblVat = 0.2
    
    If Target.Column = 1 Or Target.Column = 5 Then
    
        Application.EnableEvents = False
        
        Select Case Target.Column
            Case 1
                Res = Application.Match(Target.Value, Sheets("Data").Columns(1), 0)
        
                If Not IsError(Res) Then
                    Target.Offset(, 2).Value = Sheets("Data").Range("B" & Res).Value
                Else
                    MsgBox (Target & " not found.")
                End If
            Case 5
                If IsNumeric(Target.Value) And Target.Value <> "" Then
                    Target.Value = Target.Value / (1 + dblVat)
                End If
        End Select
        
        Application.EnableEvents = True
        
    End If
    
End Sub
Thanks so much for your help.... the data part filling in company services works ok, but the vat removal does not!..... I have added a screen shot of my spread sheet in case that will help?
David
FbQCsab7vTQteYraApR61vpvx xs7vKzvYfzO9RUnZLsB9zs DoAsRVejlFULPlgqMgQEvhRZhh0IGjQ12FGkQUEktBtSsKOe7ggCsYgTpsJHQFVjIDvbiLOJLM99K6MIwSgqT30qUytW5HY lSoDYCxeFbNeAkDGHpmYJt4oRRyzkmLqYVVPEpCyFHys9NOUd u 1HYIGDl9n7dlgS8eklL0LQhqPSMMDK1nAwEhP3t4zGhvwOO4HoRxeG0teGopTC3gaoFZvl8aqZ6n7Pmvr2ci8X8jYM2w3nff8L3A0JzbWtYQ1nH6QDDfDztv3 3cFy25ivw3DdmnnRUS RQAAAAASUVORK5CYII=
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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