.Find and return range

[Question Boy]

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

 

[JLGWhiz]

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]

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

 

[Victor Lobo]

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

 

[JLGWhiz]

Rng will return a string value for the cell identified by the Find function.
to use the data as a Range object, You can Use:

Rng.Value .Copy , Rng.Offet(#, #) =, Rng.Name =, etc.

To assign the Rng.Address to a variable and use it, then:

aRng = Rng.Address
Range(aRng) = "Hello", Range("aRng").Copy, etc.

 

[JLGWhiz]

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

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