.Find and return range

  • Thread starter Thread starter Question Boy
  • Start date Start date
Q

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
 
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
 
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
 
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
Click to expand...


ActiveCell.Address Returns a String


Try

If Not Rng Is Nothing Then
Set Find_First = Rng
Else



Hope this helps
 
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.
 
That should be just Rng.Copy, not Rng.Value.Copy

Question Boy said:
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
Click to expand...
 
Back
Top