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

Question

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

Answer 1

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.