On Sun, 18 Jan 2009 21:51:00 +0530, 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


ActiveCell.Address Returns a String


Try

If Not Rng Is Nothing Then
Set Find_First = Rng
Else



Hope this helps
--
Thanks,
Victor

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/