"object required" error when using return value from function?

Question

I have a function that finds the current date in a range and returns the cell in which it's found. I'm trying to use that cell's position to calculate an offset within a subroutine. However, when I run the macro, I get objected required as an error. The function and the subroutine are as shown:

Function findCurrentDate() As Range
    Dim needle As Date
    Dim rng As Range
    needle = CLng(Date)
    Dim haystack As Range
    Dim search As Range
    Set haystack = Range("B3:B86")
    Set search = haystack.Find(needle, After:=haystack(1), _
                           LookIn:=xlValues, Lookat:=xlWhole, _
                           SearchOrder:=xlNext, MatchByte:=True)
End Function

Sub showFutureWeeklyHours()
    Dim wkday_row_offset As Integer
    Dim search As Range
    Set search = findCurrentDate()
    Set wkday_row_offset = search.Row
...
End Sub

This isn't the complete subroutine, but it's enough to reproduce the error. The list of dates are stored in cells B3:B86

Fionnuala · Accepted Answer · 2012-06-01 19:19:28Z

2

This wkday_row_offset is a number, not an object, so do not use Set.

"This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell."

http://msdn.microsoft.com/en-us/library/ff839746.aspx

You will need to check that something is returned before looking for the row property. Nothing does not have a row. A row is a number, not an object.

Function findCurrentDate() As Range
    Dim needle As Date
    Dim rng As Range
    needle = CLng(Date)
    Dim haystack As Range
    Dim search As Range
    Set haystack = Range("B3:B86")
    Set search = haystack.Find(needle, After:=haystack(1), _
                           LookIn:=xlValues, Lookat:=xlWhole, _
                           SearchOrder:=xlNext, MatchByte:=True)
    Set findCurrentDate = search
End Function

Sub showFutureWeeklyHours()
    Dim wkday_row_offset As Integer
    Dim search As Range
    Set search = findCurrentDate()

    wkday_row_offset = search.row
''...
End Sub

edited Jun 1, 2012 at 19:19

answered Jun 1, 2012 at 18:47

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ricardo Altamirano Over a year ago

If I remove the Set keyword from the wkday_row_offset line, I get the following error: Object variable or with block variable not set Any other ideas?

Ricardo Altamirano Over a year ago

Based on your update, how would I check if something is returned? Since I tested this function several minutes ago, it does return a value because the search string 6/1/2012 does exist in the worksheet. Therefore, shouldn't the function (in this case) be returning a value?

Fionnuala Over a year ago

You need to set the function to search, set findCurrentDate = search. You said this is a fragment and asked about one error.

Ricardo Altamirano Over a year ago

Could you post a code snippet detailing what you're talking about? Changing this line: Set search = findCurrentDate() to your suggestion of: set findCurrentDate = search throws an Invalid use of property error I thought maybe you accidentally reversed it, so I tried this: Set search = findCurrentDate but that returns the same Object variable or with block variable not set error. Yes, this is a fragment, but it was enough to reproduce the first error, which is what I'm trying to fix.

Fionnuala Over a year ago

The above is your code with the suggested changes and tested as working. You should test that search is not nothing as per my link before getting the row.

|

Collectives™ on Stack Overflow

"object required" error when using return value from function?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related