.Find and return range
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.
"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
|