This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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.
Thank you for your reply. Can you please let me know how can I get the value of the country field from another excel sheet. I am having two excel sheets in sheet 1 I have all the order details including the country of shipment and in second sheet I have a table similar to one you have mentioned. I need to get the country value from sheet 1, lookup the vat for that country in sheet 2 and do the calculations based on vat.
Do get a value from the E7 cell you can do this: country = Sheet1.Range("E7").Value.
Using the VLOOKUP and other functions, sometimes it is also possible to write formulae without VBA macros: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1.