UDF Returns #Value with Table Reference

[adj87]

I have been trying to make a UDF to return salary based on various factors (COLA/Location, Job Title, Performance, etc). I have been running into issues incorporating ListObjects and assigning them to Ranges. I have looked at other forums and I don't know if this is possible, but I have tried passing the Table name in as a variable of the UDF and I get the same error. I would like to just assign the table within the UDF since the name will not change, just the range.

I had also hoped to add a match function to this so if the column being pointed to changed, I wouldn't have to change the UDF. Also, it adds additional readability to the code.

Function Test_Fnct(JobTitle As Variant, Branch As Variant, Salary As Variant, PQ As Variant) As Variant


Dim Result As Integer
Dim COLATable As Range
Dim PerfQtr As Range


COLATable = Sheets("COLA").ListObjects("COLA_Entry").Range.Select
COLATable_Head = Worksheets("COLA").ListObjects("COLA_Entry").HeaderRowRange.Select


Select Case JobTitle
    Case "JobA"
        If PQ = 1 Then
                Result = Application.WorksheetFunction.VLookup(Branch, COLATable, 6, False)
 
'In place of 6, I wanted to add a Match function:
'Application.WorksheetFunction.Match("JobA", COLATable_Head, 0)
'or at least 


            If Result <= Salary Then
                Test_Fnct = 0
            Else
                Test_Fnct = Result
            End If
            
        ElseIf PQ = 2 Then
                Result = Application.WorksheetFunction.VLookup(Branch, COLATable, 7, False)


            If Result <= Salary Then
                Test_Fnct = 0
            Else
                Test_Fnct = Result
            End If


        Else
            Test_Fnct = 0
        
        End If


    Case "JobB"
            Test_Fnct = 0
    
End Select


End Function

Any help would be greatly appreciated.
Thanks!

 

[silentwolf]

Hi,
maybe this would help?
Dim COLATABLE as range

Set COLATable=Sheet("COLA")....

range Varibles need to be set

 

[adj87]

Thanks for the suggestion, that didn't solve the issue though. Simple debug of the UDF:

Sub test()
MsgBox Test_Fnct(Range("A1").Value, Range("B1").Value, Range("C1").Value, Range("D1").Value)
End Sub

and regardless, the code stops at setting COLATable = Sheets("COLA")...