That should be just Rng.Copy, not Rng.Value.Copy

"Question Boy" wrote:

I keep getting the same bloody error!

When I place my cursor over the Rng.Address it does give the proper value
$Y$33, but for some reason it still generates an error?

I then decide to remove the 'As Range' from my function declaration so it is
now

Function Find_First(FindString As String, sht As String)

and it work but is actually return a string (I'm assuming) rather than a
range.

Functional but confused?!

QB




"JLGWhiz" wrote:

First of all, you do not need the GoTo as part of the If statement. For the
address of the found range:

Find_First = Rng.Address

"Question Boy" wrote:

I need to locate the range in a worksheet containing a specific value and
return it's range to the calling sub to be used for further processing.

I came across a find routine and started customizing it, but can't get it to
return the address of the range containing the value

Function Find_First(FindString As String, sht As String) As Range
'Dim FindString FindString
Dim Rng As Range
'FindString = InputBox("Enter a Search value")
If Trim(FindString) < "" Then
With Sheets(sht).Range("A:ZZ")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Find_First = ActiveCell.Address
Else
MsgBox "Nothing found"
End If
End With
End If
End Function

It does indeed locate the cell but at the line Find_First =
ActiveCell.Address it return an error

Run-time error '91'
Object variable or With block variable not set

What am I missing? Ultimately I would like to avoid the 'Application.Goto
Rng, True' part of the code as well. Is there a better way to locate which
cell contains a text string and return its' addres?

Thank you for the help!

QB