Find Function (vba) with Object Error

PaulWJ

New Member
Joined
Dec 4, 2023
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

VBA Code:
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?
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
VBA Code:
If Not rgFound is Nothing Then
    rgFound.Select
Else
    MsgBox "Value not found in range!"
End If
 
Upvote 0
Solution
Thanks Joe. The longer you look at something, the less likely you are to find it :)

Option Explicit now activated
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,226,227
Messages
6,189,753
Members
453,567
Latest member
kentbarbie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top