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

How do you call the function from a worksheet?

--
Kind regards,

Niek Otten

<[email protected]> wrote in message
news:[email protected]...
> 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
>

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)

<[email protected]> wrote in message
news:[email protected]...
> 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
>