Removing vat from a cell automatically

[adad67]

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

 

[Norie]

David

If you put this in a worksheet module any, numeric, value you enter in A1:A10 will have the VAT removed.

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

 

[adad67]

David

If you put this in a worksheet module any, numeric, value you enter in A1:A10 will have the VAT removed.

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

 

[Norie]

David

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

 

[adad67]

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

 

[Norie]

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?

 

[adad67]

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

 

[Norie]

David

This is rough but give it a try.

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

 

[adad67]

David

This is rough but give it a try.

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