Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not working (but works as a sub). Any ideas?
The below function works fine as a sub and returns the desired string
in the string variable FirstAddress. However, as a function, it returns a VALUE error in excel spreasheet saying that a value used in the function contains wrong data type. Is it possible to transform this code into a UDF? What I am trying to do is to provide a function that given a row keyword and a column keyword within a range (e,g, A1:K1000), it will return the value of the cell containing row keyword and column keyword coordinates, e.g. Rowsearch for "total custome - Acc.Code22" finds range of cell containing this string, say A3 Columnsearch for "Feb" finds range of cell containing this string, say C2 Hence function should return value of cell C3 I know that I could use HLOOKUP combined with MATCH but then the row and column search range would have to be fixed and if they changes you would have to redefine the range, if somebody inserted a line above the search range you would have to redefine the range by an offset of 1 row. Thanks Will Function getMyRange(searchRangeInput As Range, rValue As String) Dim FirstAddress As String Dim str As String Dim rng As Range 'Dim searchRangeInput As Range 'Dim rValue As String 'rValue = "law22" 'Set searchRangeInput = ActiveSheet.Range("A:J") Set searchRange = searchRangeInput With searchRange Set rng = .Find(What:=rValue, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then FirstAddress = rng.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) getMyRange = FirstAddress Set rng = .FindNext(rng) If rng.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) < FirstAddress Then 'Exit Function getMyRange = "DuplicateKeyWords" End If End If End With End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not working (but works as a sub). Any ideas?
How do you call the function from a worksheet?
-- Kind regards, Niek Otten wrote in message oups.com... The below function works fine as a sub and returns the desired string in the string variable FirstAddress. However, as a function, it returns a VALUE error in excel spreasheet saying that a value used in the function contains wrong data type. Is it possible to transform this code into a UDF? What I am trying to do is to provide a function that given a row keyword and a column keyword within a range (e,g, A1:K1000), it will return the value of the cell containing row keyword and column keyword coordinates, e.g. Rowsearch for "total custome - Acc.Code22" finds range of cell containing this string, say A3 Columnsearch for "Feb" finds range of cell containing this string, say C2 Hence function should return value of cell C3 I know that I could use HLOOKUP combined with MATCH but then the row and column search range would have to be fixed and if they changes you would have to redefine the range, if somebody inserted a line above the search range you would have to redefine the range by an offset of 1 row. Thanks Will Function getMyRange(searchRangeInput As Range, rValue As String) Dim FirstAddress As String Dim str As String Dim rng As Range 'Dim searchRangeInput As Range 'Dim rValue As String 'rValue = "law22" 'Set searchRangeInput = ActiveSheet.Range("A:J") Set searchRange = searchRangeInput With searchRange Set rng = .Find(What:=rValue, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then FirstAddress = rng.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) getMyRange = FirstAddress Set rng = .FindNext(rng) If rng.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) < FirstAddress Then 'Exit Function getMyRange = "DuplicateKeyWords" End If End If End With End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not working (but works as a sub). Any ideas?
This works for me
Function getMyRange(searchRangeInput As Range, rValue As String) Dim FirstAddress As String Dim str As String Dim rng As Range With searchRangeInput Set rng = .Find(What:=rValue, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then FirstAddress = rng.AddressLocal(False, False) getMyRange = FirstAddress Set rng = .FindNext(rng) If Not rng Is Nothing Then If rng.AddressLocal(False, False) < FirstAddress Then Exit Function getMyRange = "DuplicateKeyWords" End If End If End If End With End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... The below function works fine as a sub and returns the desired string in the string variable FirstAddress. However, as a function, it returns a VALUE error in excel spreasheet saying that a value used in the function contains wrong data type. Is it possible to transform this code into a UDF? What I am trying to do is to provide a function that given a row keyword and a column keyword within a range (e,g, A1:K1000), it will return the value of the cell containing row keyword and column keyword coordinates, e.g. Rowsearch for "total custome - Acc.Code22" finds range of cell containing this string, say A3 Columnsearch for "Feb" finds range of cell containing this string, say C2 Hence function should return value of cell C3 I know that I could use HLOOKUP combined with MATCH but then the row and column search range would have to be fixed and if they changes you would have to redefine the range, if somebody inserted a line above the search range you would have to redefine the range by an offset of 1 row. Thanks Will Function getMyRange(searchRangeInput As Range, rValue As String) Dim FirstAddress As String Dim str As String Dim rng As Range 'Dim searchRangeInput As Range 'Dim rValue As String 'rValue = "law22" 'Set searchRangeInput = ActiveSheet.Range("A:J") Set searchRange = searchRangeInput With searchRange Set rng = .Find(What:=rValue, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then FirstAddress = rng.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) getMyRange = FirstAddress Set rng = .FindNext(rng) If rng.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) < FirstAddress Then 'Exit Function getMyRange = "DuplicateKeyWords" End If End If End With End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
the grab and drag function isn't working in excel any ideas? | Excel Discussion (Misc queries) | |||
Fixed date with IF-function (or any other ideas?) | Excel Discussion (Misc queries) | |||
Any ideas why a reference that works will in 2003 has problems in | Excel Worksheet Functions | |||
VBA Coding - any ideas why this UDFs not working? | Excel Programming | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions |