VBA CODE ISSUE / userform including vlookup function...

[DAGUET]

Dear VBA Users,

i am stuck here and can't move : here is the issue : I have coded a userform linked to a module that uses vlookup function. The idea is simple : as the user will select a country from the drop down list (say Chile), the userform will automatically return the risk free rate and the corporate tax rate without the need to manually look it up.
The problem : my syntax sounds good but it does not work... If you have an idea that would solve this that would be really great.

another thing : when the drop down list item is selected, i would like the fields below to update automatically and not upon clicking on an empty field. This comes from a specific method I used but could be improved I think.

may many thanks (file included).

Best R

 

[Marc L]

Hi !

Bad Lookuprange which is set on active worksheet !
Just add the good worksheet reference …

And Me statement can't work within a standard module !

WorksheetFonction is a typo ! (French of course !)

Do not use Application.WorksheetFunction.Vlookup
but just Application.Vlookup …
​

 

[Marc L]

Other way is to use MATCH Excel worksheet function :​

Sub Demo()
         Const COUNTRY = "France"
    With Sheet5.[A1].CurrentRegion
        V = Application.Match(COUNTRY, .Columns(1), 0)
        If IsNumeric(V) Then MsgBox COUNTRY & " :  " & .Cells(V, 2).Text & " / " & .Cells(V, 3).Text, vbInformation, "  Demonstration :"
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​