VBA Function - Thanks, but why doens't this work...

Posted by Tim on December 28, 2001 12:09 PM

I can't figure out why this won't work. If I don't use CurrentRegion and Set an actual Range is works, but if I use the CurrentRegion Property, my function returns #Value...

Function GetBalance(ID, Period)

Dim r As Range
Set r = Sheets("ExportAmort").Range("g4").CurrentRegion
GetBalance = Application.WorksheetFunction.VLookup(ID, r, 6, 0)

End Function

Posted by Ivan F Moala on December 28, 2001 1:41 PM

Your function as given is expecting another input = period
If period is Optional then put
Function getbalance(ID,optional Period)

Ivan