Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-26-2022, 08:21 PM
soroush.kalantari soroush.kalantari is offline Need help writing UDF to determine the Row number of a value Windows 10 Need help writing UDF to determine the Row number of a value Office 2016
Competent Performer
Need help writing UDF to determine the Row number of a value
 
Join Date: Jun 2021
Posts: 115
soroush.kalantari is on a distinguished road
Default 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
Attached Files
File Type: xlsm userdifiedmatch.xlsm (17.0 KB, 8 views)

Last edited by soroush.kalantari; 07-27-2022 at 12:46 AM.
Reply With Quote
  #2  
Old 07-29-2022, 06:48 AM
p45cal's Avatar
p45cal p45cal is offline Need help writing UDF to determine the Row number of a value Windows 10 Need help writing UDF to determine the Row number of a value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #3  
Old 07-29-2022, 08:07 PM
soroush.kalantari soroush.kalantari is offline Need help writing UDF to determine the Row number of a value Windows 10 Need help writing UDF to determine the Row number of a value Office 2016
Competent Performer
Need help writing UDF to determine the Row number of a value
 
Join Date: Jun 2021
Posts: 115
soroush.kalantari is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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
Thank you veru much. The problem was solved.
Reply With Quote
  #4  
Old 07-29-2022, 11:45 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need help writing UDF to determine the Row number of a value Windows 7 64bit Need help writing UDF to determine the Row number of a value Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

If I understand correctly this can be easily solved without VBA or UDF with a simple SUMPRODUCT function

See attached
Attached Files
File Type: xlsx FindRow.xlsx (9.6 KB, 11 views)
__________________
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
Reply With Quote
  #5  
Old 08-05-2022, 07:42 PM
soroush.kalantari soroush.kalantari is offline Need help writing UDF to determine the Row number of a value Windows 10 Need help writing UDF to determine the Row number of a value Office 2016
Competent Performer
Need help writing UDF to determine the Row number of a value
 
Join Date: Jun 2021
Posts: 115
soroush.kalantari is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
If I understand correctly this can be easily solved without VBA or UDF with a simple SUMPRODUCT function

See attached
Yes. you are right. This problem can be solved with your approach without VBA. I didn’t understand the sumproduct can be used in such ways.
No. I didn’t understand the way you mention to thank to somebody to help me. Thank you very much for all the points.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help writing UDF to determine the Row number of a value 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
Need help writing UDF to determine the Row number of a value Determine if a cell contains a 0 value bgranzow Word VBA 1 01-07-2016 03:50 PM
Need help writing UDF to determine the Row number of a value 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
Need help writing UDF to determine the Row number of a value Edit number of characters per page after writing whole paper dea Word 1 01-04-2011 02:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:16 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft