Help with creating a UDF

[RBG]

Good day:

I'm trying to create my first UDF to replace some formulas that are 500+ characters long. The objective is to use Vlookup to find a value in one table, do some arithmetic on that value, look up another value in another table and do some arithmetic on it, then do some more arithmetic on the results of those and give a result.

Start small and learn: created a table A1:B10 named test1 with 1 to 10 in Col A then 10 to 100 in increments of 10 in Col B. In Cell A13 is the value I want to look up (i.e <3>)with the UDF in Cell B13 =TestUDF(A13) expecting a result of <30>.

I am getting the error #VALUE.


Function TestUDF(Lookup As Integer) As Integer
TestUDF = Application.WorksheetFunction.VLookup(Lookup, test1, 2)

End Function

Any insights?

Thanks,
Rod

[olasa]

This example might help:

Option Explicit
' Example =LOOKUPV (E1, A1:C10, 2, FALSE, "")
Function LOOKUPV(Lookup_Value, Table_Array As Range, Col_Index_Num, Range_value, Optional Error_Msg)
LOOKUPV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num, Range_value)
If IsError(LOOKUPV) And Not IsMissing(Error_Msg) Then LOOKUPV = Error_Msg
End Function

Should be 6 rows


Ola Sandström

[RBG]

Ola:

It's like magic...works like a hot damn! Thank you.

Now I'll apply this to successfully more complicated scenarios until I get to the specific obstacle that I want to overcome.

Rod