Vlookup in VBA?

[welshgasman]

Hi all,
I am trying to created a UDF for a complicated (to me) calculation.

In the function I have

Function CalcFee(TxAmount As Currency) As Currency


Dim iFeeType As Integer


'FeeType indicates whether a set value £200 or a %
iFeeType = WorksheetFunction.VLookup(TxAmount, Sheets("Lookup").Range("A2:E6"), 5, True)
If iFeeType = 3 Then
    IFAFee = Sheets("Lookup").Range("D7").Value
Else
    IFAFee = Round(TxAmount * Sheets("Lookup").Range("D7").Value, 2)
End If


End Function

TxAmount is passed to the function. let's make it 22000 for this example

if I try ? WorksheetFunction.VLookup(TxAmount, Sheets("Lookup").Range("A2:E6"), 5, True) after setting TxAmount in the debug window, I get the correct result.

However when I try ? CalcFee(22000) I get

Run-time error 1004
Unable to get the Vlookup property of the worksheet function class

This is only the start of the code, but I am relying on picking up this value for further calculations?

What am I missing please?

TIA

 

[skorpionkz]

Hi welshgasman,

just change "as currency" to "as double"

Function CalcFee(TxAmount As Double) As Currency

Dim iFeeType As Integer

'FeeType indicates whether a set value £200 or a %
iFeeType = WorksheetFunction.VLookup(TxAmount, Sheets("Lookup").Range("A2:E6"), 5, 0)
If iFeeType = 3 Then
    CalcFee = Sheets("Lookup").Range("D7").Value
Else
    CalcFee = Round(TxAmount * Sheets("Lookup").Range("D7").Value, 2)
End If

End Function

 

[welshgasman]

Doh!

Thank you skorpionkz