"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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related