Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and return range
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find first and last cells in range, return info beside | Excel Worksheet Functions | |||
Find value between range & return text | Excel Worksheet Functions | |||
Find and return data based on a range | Excel Programming | |||
find the first blank cell in a range and return me it's position | Links and Linking in Excel | |||
VBA to find Cell Range in Files in Folder, return value | Excel Programming |