Find Function (vba) with Object Error

[PaulWJ]

I've got the following code in a workb0ok consisting of a number of sheets. The purpose of the code is to look for the date (dtsearch, which is on one worksheet), and look for it one another worksheet. There are six similar worksheets, but it will only be found on the active one (activeweek) which is known at the outset. I then want it to select that cell (so I can do other things) The code appears to work fine, until the last two lines when I get an error, Run-time error '91' Object variable with block variable not set

Sub Status_Fill()

' Fills in the Status of each individual based on the date being allocated for

Dim dtsearch As Date
Dim activeweek As String
Dim rgFound As Range

dtsearch = Sheets("Background").Range("b29").Value ' DAte being searched
activeweek = Sheets("Background").Range("b30").Value ' Confirms the sheet to be looked in

Worksheets(activeweek).Select
rgFound = Range("C6:G6").FIND(dtselect, LookIn:=xlValues)

rgFound.Select


End Sub

Apologies, I cant seem to get the sheet to upload. Both the original B29 cell, and all the cells in C6:G6 are formatted as Date cells. The Range consists of formulae (to get the dates), hence me adding the xlValues option.

Any suggestions?

 

[Joe4]

Your variable is named "dtsearch", but you are using "dtselect" in your Find command.

I highly recommend turning on Option Explicit, which forces you to declare all variables before using them, and will catch typos like this.
See: Option Explicit in Excel VBA

Also, you should check to make sure you found a match before trying to select the range, otherwise you are trying to select an empty range and will get errors.

If Not rgFound is Nothing Then
    rgFound.Select
Else
    MsgBox "Value not found in range!"
End If

 

[PaulWJ]

Thanks Joe. The longer you look at something, the less likely you are to find it

Option Explicit now activated

 

[Joe4]

You are welcome.
Glad I was able to help!

Yes, Option Explicit has been a lifesaver for me, as I am a "less-than-stellar" typist myself. Saves me time in trying to find all my typos in my code!