Run Time Error '91'

[lydiapenrose]

Hi. So I wrote the code below. It's really simple. I am trying to have it so users can input the start date of a work week. The code then finds this date and eventually I plan to use offset to copy and past and sum diferent work hours into a format that we need for weekly presentations.

However, when I try to run it, the input box comes up fine and then after I input my date, I get Run Time Error '91': Object variable or With block variable not set. I tried researching this error but I haven't set any objects/don't understand what I'm doing wrong. Please help! My code is below. Thanks

Sub Timesheet()
'
' Macro1 Macro
'
Dim DateEntry As String
DateEntry = InputBox("Enter the first date in your five day date range in the format mm/dd/yyyy.", "Beginning Date")
'

Cells.find(What:="DateEntry", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


End Sub

 

[Trevor G]

Welcome to the Forum,

You are looking for a date, so you don't declare your object as string, should be date. Also DON'T place quotes around the object name

This worked for me.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Needs to declared as date not string</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DateEntry <SPAN style="color:#00007F">As</SPAN> Date<br>DateEntry = InputBox("Enter the first date in your five day date range in the format mm/dd/yyyy.", "Beginning Date")<br><SPAN style="color:#007F00">'When referencing name in find DON'T add the quotes "DateEntry"</SPAN><br>    Cells.Find(What:=DateEntry, After:=ActiveCell, LookIn:=xlFormulas, _<br>        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _<br>        MatchCase:=False, SearchFormat:=False).Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[GTO]

Hi. So I wrote the code below. It's really simple. I am trying to have it so users can input the start date of a work week. The code then finds this date and eventually I plan to use offset to copy and past and sum diferent work hours into a format that we need for weekly presentations.

However, when I try to run it, the input box comes up fine and then after I input my date, I get Run Time Error '91': Object variable or With block variable not set. I tried researching this error but I haven't set any objects/don't understand what I'm doing wrong...

Hi there,

You have a couple of issues.

1. You need to ditch the quote marks around 'DateEntry' in the What:= parameter. With the quote marks, .Find is literally trying to find "DateEntry."
2. If that does not solve it, I would suspect that your regional settings probably are m/d/yyyy. That is to say, that regardless of how you have a cell formatted, for instance, mm/dd/yyyy, when you look in the formula bar, the date in the cell will return as m/d/yyyy. Let us say that you have January 30, 2011 entered in a cell in mm/dd/yyyy format. So in the cell, you see '01/30/2011'. With this cell selected, look in the formula bar. Is it '1/30/2011' in the formula bar?
3. This leads to where we are telling .Find to look. You have LookIn:=xlFormulas, which means .Find is looking in the formula bar, if that makes sense.
4. Finally, your code should be able to handle not finding the value sought. That is, something like:

Sub Timesheet()
Dim rngFound As Range
Dim DateEntry As String
    
    DateEntry = InputBox("Enter the first date in your five day date range in the format mm/dd/yyyy.", "Beginning Date")
    
    If IsDate(DateEntry) Then
        
        Set rngFound = Cells.Find(What:=Format(DateEntry, "m/d/yyyy"), _
                                  After:=ActiveCell, _
                                  LookIn:=xlFormulas, _
                                  LookAt:=xlPart, _
                                  SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext _
                                  )
    End If
    
    If Not rngFound Is Nothing Then
        rngFound.Select
    End If
End Sub

Hope that helps

Mark

 

[Richard Schollar]

It's a good idea to check to see if the date is actually found before you try and activate the found cell - this way you will avoid an error if the date isn't present:

Dim rFound As range
 
'all else as per Trevor
'...
 
Set rFound = Cells.Find(What:=DateEntry, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
 
'now check to see if the date was found:
 
If Not rFound Is Nothing Thne
  'date found - do stuff eg:
  rFound.Activate
Else
  'date not found - take appropriate action!
  MsgBox "Date not found in sheet! Exiting Sub..."
  Exit Sub
End If

There was no validation that whatever is plonked into the Inputbox is also an actual date, so this may be a potential source of error too.

 

[lydiapenrose]

Oh my goodness. Thank you all! I can now continue on in this project and future endeavors in macros.

Really, I appreciate your quick and super helpful responses!
Enjoy your day/night depending on where you are!

 

[lydiapenrose]

Aaaand another hangup. So now using the rngFound as a reference point, I am trying to navigate around the current excel sheet, copy things and paste it into a new workbook. However, when I ask for a msgBox showing rngFound's value, it gives me the date that I inputted earlier rather than the cell that it resides in, which is what I expected. How do I get that range to be set as rngFound?

Thanks in advance!

Below is some sample code trying to paste the found cell into a new workbook.

Sub Timesheet()
Dim rngFound As Range
Dim DateEntry As String
Dim BaseWks As Worksheet

DateEntry = InputBox("Enter the first date in your five day date range in the format mm/dd/yyyy.", "Beginning Date")

If IsDate(DateEntry) Then

Set rngFound = Cells.find(What:=Format(DateEntry, "m/d/yyyy"), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext _
)

If Not rngFound Is Nothing Then
'date found - do stuff eg:
rngFound.Activate

' Add a new workbook with one sheet.
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1
rngFound.Select
Selection.Copy
BaseWks.Range("B1").Select
ActiveSheet.Paste



Else
'date not found - take appropriate action!
MsgBox "Date not found in sheet."
Exit Sub
End If
End If

End Sub

 

[Richard Schollar]

Do you perhaps want to access the Address property of rngFound?

MsgBox rngFound.Address

 

[GTO]

Aaaand another hangup. So now using the rngFound as a reference point, I am trying to navigate around the current excel sheet, copy things and paste it into a new workbook. However, when I ask for a msgBox showing rngFound's value, it gives me the date that I inputted earlier rather than the cell that it resides in, which is what I expected. How do I get that range to be set as rngFound?

Hi Lydia,

rngFound is a Set reference to a range, in this case, one cell (where we found the date sought). When you used the MsgBox to return rngFound.Value, it correctly returned the range's .Value property. As Richard notes, you could also return the cell's address, or a number of other properties. Given your sample code, I would note where it will fail; not because rngFound is not set, but because a new wb's sheet is now active and .Select will only work against the activesheet.

Sub Timesheet2()
Dim rngFound    As Range
Dim DateEntry   As String
Dim BaseWks     As Worksheet
Dim rnum        As Long
    
    DateEntry = InputBox("Enter the first date in your five day date range in the format mm/dd/yyyy.", "Beginning Date")
    
    If IsDate(DateEntry) Then
    
        Set rngFound = Cells.Find(What:=Format(DateEntry, "m/d/yyyy"), _
                                  After:=ActiveCell, _
                                  LookIn:=xlFormulas, _
                                  LookAt:=xlPart, _
                                  SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext _
                                  )
        
        If Not rngFound Is Nothing Then
        
            'date found - do stuff eg:
            rngFound.Activate
            
            ' Add a new workbook with one sheet.
            Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
            rnum = 1
            
            '***This will fail, as the added wb now has the focus (that is, it is active, and
            '   thus, one of the new wb's sheets is active.  Selecting only works on the
            '   active sheet.
            'rngFound.Select
            'Selection.Copy
            
            '// But you can skip most selection/active/selecting/activating.    //
            'BaseWks.Range("B1").Select
            'ActiveSheet.Paste
            
            rngFound.Copy BaseWks.Range("B1")
        
        Else
            'date not found - take appropriate action!
            MsgBox "Date not found in sheet."
            Exit Sub
        End If
    End If
End Sub

When posting code, please use the [code=rich] '...your code goes here[/code] tags around the code. This will make your code a lot easier to read

Mark

 

[lydiapenrose]

Beautiful!
Thank you so much everyone.