#1




Need help writing UDF to determine the Row number of a value
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 Last edited by soroush.kalantari; 07272022 at 12:46 AM. 
#2




Change your formula in cell B2 to say:
=matchrow(A2,$C$1:$N$10) copy down if you want. Put values you're looking for in column A next to the formulae. Remove all the Dims at the top of the codemodule. Change your function to: 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 
#3




Quote:

#4




If I understand correctly this can be easily solved without VBA or UDF with a simple SUMPRODUCT function
See attached
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post 
#5




Quote:
No. I didn’t understand the way you mention to thank to somebody to help me. Thank you very much for all the points. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How to determine if a Section number has a field code  scienceguy  Word VBA  8  11112021 05:19 AM 
Word writing {PAGE} instead of inserting page number  Johanna  Word  5  07042019 07:56 AM 
Determine if a cell contains a 0 value  bgranzow  Word VBA  1  01072016 03:50 PM 
Is there a way programmatically to determine the footnote number style, if other than arabic numbers  gn4619  Word VBA  2  11232015 11:00 PM 
Edit number of characters per page after writing whole paper  dea  Word  1  01042011 02:24 AM 