My routine work in Excel is such that I should constantly determine the Row Number of some values. Because, usually I do not know exactly know where are the columns of these variables, I cannot use the Excel match function. Therefore, I am trying to create a UDF function to meet my needs. I have written following macro, but it does not give the expected result. (I have learnt the “range. Row” prosperity of VBA dos not work in UDF functions as it does in SUB. The values which I want to determine their rows are unique and occur just one time and I can determine their approximate position or alternatively I can make ”x” large enough to ensure it contains “x”)

Option Explicit
Dim interval As Range
Dim column As Integer
Dim i As Integer
Dim x As Variant
Dim n As Integer
Dim y As Integer

Function matchrow(x, interval)
column = interval.collums.Count
n = interval.rows(1).Row
For i = 1 To column

y = Application.WorksheetFunction.Match(x, interval.columns(i), 0)

If y > 0 Then
Exit For
End If


Next i
matchrow = y + n
End Function

Attached Files

userdifiedmatch.xlsm (17.0 KB, 8 views)