View Single Post
 
Old 08-11-2022, 04:01 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
 
Join Date: Apr 2014
Posts: 648
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

You've Dimmed all variables at module level. I suspect it's to do with that although I can't reproduce the problem here.
only Dim the variables you need to at the function level:
Code:
Function matchrow(x, interval)
Dim a As Range
Set a = interval.Find(x, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, searchformat:=False)
If Not a Is Nothing Then matchrow = a.Row Else matchrow = "Not found"
End Function

Function matchcollum(y, interval)
Dim a As Range
Set a = interval.Find(y, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, searchformat:=False)
If Not a Is Nothing Then matchcollum = a.Column Else matchcollum = "Not found"
End Function


Function vlookupUDF(x, y, interval)
Dim nrow As Long, ncollumn As Long
nrow = matchrow(x, interval)
ncollumn = matchcollum(y, interval)
vlookupUDF = Cells(nrow, ncollumn)
End Function
See if that helps.
Reply With Quote