#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; 07-27-2022 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 code-module. 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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to determine if a Section number has a field code | scienceguy | Word VBA | 8 | 11-11-2021 05:19 AM |
Word writing {PAGE} instead of inserting page number | Johanna | Word | 5 | 07-04-2019 07:56 AM |
Determine if a cell contains a 0 value | bgranzow | Word VBA | 1 | 01-07-2016 03:50 PM |
Is there a way programmatically to determine the footnote number style, if other than arabic numbers | gn4619 | Word VBA | 2 | 11-23-2015 11:00 PM |
Edit number of characters per page after writing whole paper | dea | Word | 1 | 01-04-2011 02:24 AM |