UDF to get a table's header cell value, based on the variables

[drom]

Hi and thanks in advance!

I am trying to create a UDF


My UDF does not work:

Function myUDF(rSCORE As Range, rYEAR As Range, rGENDER As Range, Select_SHEET As Range)
  Dim lObj As ListObject
Dim wSHT As String:                        wSHT = Select_SHEET.Parent.Name
Dim wYEAR As String:                        wYEAR = rYEAR
dim wScore as String:                        wScore= rSCORE
dim wGender as string:                    wGender=lCase(Right(rGENDER,2))
Dim rTable As Range
Dim rCol As Range
Dim rRow As Range


  For Each lObj In Sheets(wSHT).ListObjects
    If LCase(Right(lObj, 2)) = wGender  Then
      Set rTable = lObj.Range
      Set rCol = rTable.Columns(1):        Set rRow = rTable.Rows(1)
      Exit For
    End If
  Next lObj

 
  xRow = Application.WorksheetFunction.Match(wYEAR, rCol)

  xCOL = Application.WorksheetFunction.Match(SCORE, rTable.Rows(xRow))
 
  myUDF = rRow.Cells(xCOL)
 
End Function

my UDF does not work, iguess by my variables you know what I am expecting

Thanks again

 

[NARAYANK991]

Hi ,

It would help if you could upload your workbook with the data and the code in it.

Also can you explain what problem you face in your UDF ?

Just BTW , your MATCH statements should make use of the third parameter , which can be -1 , 0 or 1 ; if you do not specify this , Excel uses a default value , which may not be what you intended.

Narayan

 

[drom]

xRow = Application.WorksheetFunction.Match(cdbl(wYEAR), rCol)

xCOL = Application.WorksheetFunction.Match(cdbl(SCORE), rTable.Rows(xRow))

Does the job