What am I doing wrong? (returning Range from function.

[Jeffrey Harris]

I have the following function

Function GetRowWithDate(aDate As Date) As Range
dateRange = Worksheets("Prices").Range("A3:A" &
Worksheets("Prices").UsedRange.Rows.Count)
For Each cell In dateRange
If cell = aDate Then
Set GetRowWithDate = Worksheets("Prices").Range("A" & cell.Row &
"" & cell.Row) <-- line with error.
Exit Function
End If
Next cell

Set GetRowWithDate = Nothing
End Function

When I call the function by 'oldDate = GetRowWithDate("10/27/03")' I get a
"Run-time error '404': Object required" error on the line I've flagged. I'm
new to VBA programming, what concept am I not understanding? And how do I
get this to return the range?

 

[J.E. McGimpsey]

This is a case when having Option Explicit at the top of your module
(In the VBE: Tools/Options, check the Require variable declaration
checkbox) and declaring your variable types would have helped
immensely. I'd recommend you do this with all your code.

Since you didn't declare the dateRange variable type, it's initiated
as a Variant. The daterange = ... assignment places an array of the
values (i.e. the range default property) of the range into the
variant, not the range itself. Object variables must be assigned
using the Set statement.

If you'd declared dateRange to be a Range object variable, you would
have set dateRange to refer to the range.

Likewise, since dateRange is an array of values, the variant cell
variable will be assigned a value rather than a range object. This
is what finally causes the error, since values don't have .Row
properties.

Here's one way to correct your code:

Option Explicit
Function GetRowWithDate(aDate As Date) As Range
Dim daterange As Range
Dim cell As Range
With Worksheets("Prices")
Set daterange = .Range("A3:A" & .UsedRange.Rows.Count)
For Each cell In daterange
If cell = aDate Then
Set GetRowWithDate = .Range( _
"A" & cell.Row & "" & cell.Row)
Exit Function
End If
Next cell
End With
Set GetRowWithDate = Nothing
End Function

Note that I used the With...End With construction to save typing and
to make the code a bit more efficient (since it only has to resolve
Worksheet("Prices") once).

 

[Jeffrey Harris]

Bingo, your suggestions did the trick. Thanks for the help (both with this
and my previous 'For Each..Next' question)

Jeff

 

[J.E. McGimpsey]

one typo in my previous post...


Should read:

If you'd declared dateRange to be a Range object variable, you
would have been prompted to use set dateRange to refer to the range.