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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and return range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and return range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and return range
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and return range
That should be just Rng.Copy, not Rng.Value.Copy
"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 |
Reply |
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 |