UDF to return RowNumber

aboons

Board Regular
Joined
Mar 14, 2007
Messages
79
Hi Ya,

I've created this function that's supposed to return the number of a row in one sheet that has the value in it that's looked for in the sheet that has the UDF in it.

It works when I test it via a procedure, but it doesn't when I enter the UDF in a cell.

Any help would be greatly appreciated.


Code:
Function ConvertsDateToRow(DValue) As Variant

With Sheets("CLEAN_DATA_PER_DATE").Range("ca1:ca3084")
    Set FoundDate = .Find(what:=DValue)
End With

ConvertsDateToRow = FoundDate.Row

End Function
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,956
I think this would work:
=MATCH(serialDate,CA:CA,0)

Also, UDF's called from the spreadsheet can't use the .FIND method. It returns a #VALUE! error, just as the .NavigateArrow method does.
 
Last edited:

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
As Mike shows, you don't need a UDF, but if you do write UDFs you should not hardcode ranges in them. Use something like

Code:
Function ConvertsDateToRow(Target As Range, DValue) As Variant
Dim FoundDate As Range

    On Error Resume Next
    Set FoundDate = Target.Find(what:=DValue)
    If Not FoundDate Is Nothing Then
        ConvertsDateToRow = FoundDate.Row
    End If
End Function

and call like

=ConvertsDateToRow('CLEAN_DATA_PER_DATE'!CA1:CA3084),TODAY())
 

aboons

Board Regular
Joined
Mar 14, 2007
Messages
79

ADVERTISEMENT

And thanks also for your explanation on the UDF.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,273
Messages
5,623,757
Members
415,987
Latest member
alysse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top