Defining Find Function

[T De Villiers]

Hi,

Im trying to creating functions to find variables,
the below isnt quite working, many thanks


Function finrows(x As String)


Cells.FIND(What:="x", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Function


Function fincols(x As String)


Cells.FIND(What:="x", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Function

[colofnature]

Hmm, strange. It doesn't seem to be possible to use the Find command within a function. If you're trying to return the row/column number of something within a macro, you could use a module level variable to hold the value. This defines a routine "find_row_or_column" to return the row (default or "return_column" = False) or column ("return_column" = True or <> 0), or 0 if not found, to the variable "found_row_or_column":

Please Login or Register  to view this content.

To return this from a UDF usable within a worksheet would probably involve spanning the usedrange a cell at a time...


Col

[Carim]

Hi,

Find started working in xl2002. Before that, it didn't work in UDFs.

HTH
Cheers
Carim