UDF Custom function works in immediate window but not worksheet cell

[LeroyWork]

Hi All,

I have written this UDF which returns the value 2 as expected. (formula in cell "=TEST1(V2)")

Function TEST1(A)
TEST1 = Application.VLookup(A, Sheets("pivots").Range("v2:w60"), 2, 0)
End Function

However when I use the function "vlookupGANum2(A As String)" the immediate window returns the correct result but not the cell. L The cell returns zero (0)

Function vlookupGANum2(A As String).
Dim GANum2 As Integer
Dim pT2 As PivotTable

Set pT2 = Sheets("pivots").PivotTables("GA")

GANum2 = Application.VLookup(A, pT2.TableRange1.Offset(1, 0).Resize(pT2.TableRange1.Rows.Count - 1), 2, 0)
Debug.Print GANum2

End Function

Am I doing something wrong?

 

[lrobbo314]

Your function name doesn't match when you are returning the result.

Function vlookupGANum2(A As String).
Dim GANum2 As Integer
Dim pT2 As PivotTable

Set pT2 = Sheets("pivots").PivotTables("GA")

vlookupGANum2 = Application.VLookup(A, pT2.TableRange1.Offset(1, 0).Resize(pT2.TableRange1.Rows.Count - 1), 2, 0)

End Function

 

[LeroyWork]

Your function name doesn't match when you are returning the result.

Function vlookupGANum2(A As String).
Dim GANum2 As Integer
Dim pT2 As PivotTable

Set pT2 = Sheets("pivots").PivotTables("GA")

vlookupGANum2 = Application.VLookup(A, pT2.TableRange1.Offset(1, 0).Resize(pT2.TableRange1.Rows.Count - 1), 2, 0)

End Function

Thanks. So simple. I feel a little embarassed for not picking it up. thanks again

 

[lrobbo314]

No problemo. Happens to everyone.