I am getting and error as shown ' Object Variable Not Set' But I have a
valid range as I can see its properties in the watch window, 'key' is the
value I am searching for and this is alos valid. I know I have the right
range as It has the right number of cells in the cell count.

Any Ideas ?

Public Function lookupRange(ByRef rangeName As Range, key As Variant, col As
Presets) As Variant

Dim rowIndex As Integer
Dim myStr As String
Dim OpenBrktPos As Long
Dim CloseBrktPos As Long
Dim startRange As Range

Set startRange = rangeName.Cells(1, 1)

' ***** LINE BELOW CAUSES THE ERROR *********
myStr = rangeName.Find(what:=key,
SearchOrder:=xlByColumns).Address(False, False, xlR1C1, False, startRange)

OpenBrktPos = InStr(1, myStr, "[", vbTextCompare)
CloseBrktPos = InStr(1, myStr, "]", vbTextCompare)
rowIndex = CInt(Mid(Left(myStr, CloseBrktPos - 1), OpenBrktPos + 1)) + 1

lookupRange = Range(rangeName).Cells(rowIndex, col)


End Function

I know what the error was caused by now, it threw the error because it could
not find an object associated with the key.

Not sure how best to handle that , any ideas ?




"Goofy" wrote in message
...
I am getting and error as shown ' Object Variable Not Set' But I have a
valid range as I can see its properties in the watch window, 'key' is the
value I am searching for and this is alos valid. I know I have the right
range as It has the right number of cells in the cell count.

Any Ideas ?

Public Function lookupRange(ByRef rangeName As Range, key As Variant, col
As Presets) As Variant

Dim rowIndex As Integer
Dim myStr As String
Dim OpenBrktPos As Long
Dim CloseBrktPos As Long
Dim startRange As Range

Set startRange = rangeName.Cells(1, 1)

' ***** LINE BELOW CAUSES THE ERROR *********
myStr = rangeName.Find(what:=key,
SearchOrder:=xlByColumns).Address(False, False, xlR1C1, False, startRange)

OpenBrktPos = InStr(1, myStr, "[", vbTextCompare)
CloseBrktPos = InStr(1, myStr, "]", vbTextCompare)
rowIndex = CInt(Mid(Left(myStr, CloseBrktPos - 1), OpenBrktPos + 1)) +
1

lookupRange = Range(rangeName).Cells(rowIndex, col)


End Function

Untested:

rangeName is already a range. So don't wrap it in range(rangename).

lookupRange = Range(rangeName).Cells(rowIndex, col)
becomes
lookupRange = rangeName.Cells(rowIndex, col)



Goofy wrote:

I am getting and error as shown ' Object Variable Not Set' But I have a
valid range as I can see its properties in the watch window, 'key' is the
value I am searching for and this is alos valid. I know I have the right
range as It has the right number of cells in the cell count.

Any Ideas ?

Public Function lookupRange(ByRef rangeName As Range, key As Variant, col As
Presets) As Variant

Dim rowIndex As Integer
Dim myStr As String
Dim OpenBrktPos As Long
Dim CloseBrktPos As Long
Dim startRange As Range

Set startRange = rangeName.Cells(1, 1)

' ***** LINE BELOW CAUSES THE ERROR *********
myStr = rangeName.Find(what:=key,
SearchOrder:=xlByColumns).Address(False, False, xlR1C1, False, startRange)

OpenBrktPos = InStr(1, myStr, "[", vbTextCompare)
CloseBrktPos = InStr(1, myStr, "]", vbTextCompare)
rowIndex = CInt(Mid(Left(myStr, CloseBrktPos - 1), OpenBrktPos + 1)) + 1

lookupRange = Range(rangeName).Cells(rowIndex, col)

End Function

--

Dave Peterson

Thanks Dave

I'd already gotten to this one, but thanks for replying


"Dave Peterson" wrote in message
...
Untested:

rangeName is already a range. So don't wrap it in range(rangename).

lookupRange = Range(rangeName).Cells(rowIndex, col)
becomes
lookupRange = rangeName.Cells(rowIndex, col)



Goofy wrote:

I am getting and error as shown ' Object Variable Not Set' But I have a
valid range as I can see its properties in the watch window, 'key' is the
value I am searching for and this is alos valid. I know I have the right
range as It has the right number of cells in the cell count.

Any Ideas ?

Public Function lookupRange(ByRef rangeName As Range, key As Variant, col
As
Presets) As Variant

Dim rowIndex As Integer
Dim myStr As String
Dim OpenBrktPos As Long
Dim CloseBrktPos As Long
Dim startRange As Range

Set startRange = rangeName.Cells(1, 1)

' ***** LINE BELOW CAUSES THE ERROR *********
myStr = rangeName.Find(what:=key,
SearchOrder:=xlByColumns).Address(False, False, xlR1C1, False,
startRange)

OpenBrktPos = InStr(1, myStr, "[", vbTextCompare)
CloseBrktPos = InStr(1, myStr, "]", vbTextCompare)
rowIndex = CInt(Mid(Left(myStr, CloseBrktPos - 1), OpenBrktPos + 1))
+ 1

lookupRange = Range(rangeName).Cells(rowIndex, col)

End Function

--

Dave Peterson