Excel VBA for comparing an input data with a range of values and give an output

Anjali Santhosh 21 Reputation points

I am working on a macros for generating invoices. I have a list of countries that has different VAT values. Now I want to write a function that can compare the country of shipment to the values in excel sheet and return the percentage of vat value corresponding to that country. I am new to VBA and stuck with this.
Thanks for any help in advance

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,671 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113.9K Reputation points

    For example, you have the following data in worksheet called "Sheet1", at range A1...B4:

    Country1  |  10 |
    Country2  |  14 |
    Country3  |  12 |
    Country4  |  20 |

    Then you can use the next example:

    Dim country As String
    country = "Country2"
    Dim VAT As Double
    VAT = -1
    On Error Resume Next
    VAT = Application.WorksheetFunction.VLookup(country, Sheet1.Range("A1:B4"), 2, False)
    On Error GoTo 0
    MsgBox VAT

    It returns the value of the second column based on country variable, or -1 if the country is not found.

    Adjust it for your case. Show more real details if it does not work.

0 additional answers

Sort by: Most helpful