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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Upvote 0
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())
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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
Back
Top