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

Anjali Santhosh 21 Reputation points
2021-11-23T08:25:55.247+00:00

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,343 questions
No comments
{count} votes

Accepted answer
  1. Viorel 82,741 Reputation points
    2021-11-23T09:42:34.493+00:00

    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