Calling a UDF with variable arguments within a produce

[PVA0724]

Hi,

I have the following funtion from the guru files page

Function VLOOKNEW(lookup_value, table_array As Range, _
           col_index_num As Integer, CloseMatch As Boolean)
' Allows for col_index_num to be negative
' that matches the lookup value.
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
    VLOOKNEW = "Not Found"
    ' if positive, treat as a regular VLOOKUP
    If col_index_num > 0 Then
        VLOOKNEW = Application.WorksheetFunction.VLookup(lookup_value, _
            table_array, col_index_num, CloseMatch)
    Else
        ' Do a VLOOKUP Left
        nRow = Application.WorksheetFunction.Match(lookup_value, _
            table_array.Resize(, 1), CloseMatch)
        VLOOKNEW = table_array(nRow, 1).Offset(0, col_index_num)
    End If
End Function

which actually works great, now I'm trying to call that funtion into a new code like this

Sub Data()
Dim wks As Worksheet
Set wks = ActiveSheet
ult = wks.Cells(1048576, 1).End(xlUp).Row
For i = 2 To ult
        If wks.Cells(i, 1) <> "" Then
            wks.Cells(i, 11) = Application.Run(VLOOKNEW, wks.Cells(i, 8), Sheets("ICP Participants").Columns(2), -1, False)
        End If
Next i
End Sub

But I'm getting the error argument not optional, when I look the sintax it shows that the first one has to be the macro (I assumed here it was the funtion name?) or I'm using wrongly the "Application.Run" as I don't know any other, I'm needing assistance to help me with a new approach here.

Thanks in advance.

 

[rjwebgraphix]

No need for application.run just call the UDF.

try this.....

wks.Cells(i, 11) = VLOOKNEW(wks.Cells(i, 8), Sheets("ICP Participants").Columns(2), -1, False)

Also double check your column index number. Are you sure -1 is what you want? Looks to me like it would be looking at a column less than column A, since column A's index number is 1. so you might want to double check what you need there.

 

[PVA0724]

Hi

Thanks for the solution, it worked...about the -1 in the formula, this is driven by the funtion where it looks on the left to the range.

Regards

 

[rjwebgraphix]

Hi

Thanks for the solution, it worked...about the -1 in the formula, this is driven by the funtion where it looks on the left to the range.

Regards

Ahh, ok. I didn't look at it that closely, just enough to see the UDF call. Glad you got it working.