Run-Time error '91': Object variable or With block variable not set

[LMjdm]

So basically I am at the final stages of creating an excel spreadsheet that generates the fiscal month based on the date give and will assign different tasks to various groups based on the day. I am almost finished the calendar portion but am having a problem in this section

'Below is the section for finding the current day and highlighting it
Dim whatsToday As Integer 'day value for today
Dim currentFiscalMonth As Integer 'whether it is fiscal date of current month or next month
Dim actualMonth As Integer 'regardless of fiscal month, this generates actual month
whatsToday = Sheets("Master Dial").Range("L2") 
currentFiscalMonth = Sheets("Master Dial").Range("m2")
actualMonth = Sheets("Master Dial").Range("L1")
 
If actualMonth = currentFiscalMonth Then 'if both fiscal and actual month are the same, highlighted day will be after the 1st
    
    Sheets("Master Dial").Range("G4:H4, B6:H6, B8:H8, B10:H10, B12:B12").Find(whatsToday).Select
    ActiveCell.Interior.ColorIndex = 6
    
Else 'if both fiscal and actual month are the same, highlighted day will be before the 1st
    
    If Sheets("Master Dial").Range("b4") <> 1 Then
    Sheets("Master Dial").Range("B4:H4").Find(whatsToday).Select
    ActiveCell.Interior.ColorIndex = 6
    End If
End If

I get the error listed in the title and I don't understand why. Basically this portion of the code is going to look through the calendar and find the date based on what the current day is, and simply highlight it to mark it as "today" the reference is correct (L2). Meaning that I have a cell with the value of todays date and that cell is ALWAYS correct. So I know that's not the issue. The line I believe the error is occurring on is "Sheets("Master Dial").Range("G4:H4, B6:H6, B8:H8, B10:H10, B12:B12").Find(whatsToday).Select" For this example, please assume that whatsToday = 26
currentFiscalMonth = 6
actualMonth = 6

 

[LMjdm]

To clarify, I am using excel 2010, and thanks in advance to anyone who can assist. I may just have to the whole section over if I can figure this out and that would be quite annoying lol

 

[dmt32]

Hi,
see if this change to your code helps:

'Below is the section for finding the current day and highlighting it
    Dim whatsToday As Integer    'day value for today
    Dim currentFiscalMonth As Integer    'whether it is fiscal date of current month or next month
    Dim actualMonth As Integer    'regardless of fiscal month, this generates actual month
    Dim Found As Range


    With Sheets("Master Dial")
        whatsToday = .Range("L2").Value
        currentFiscalMonth = .Range("m2").Value
        actualMonth = .Range("L1").Value


        If actualMonth = currentFiscalMonth Then    'if both fiscal and actual month are the same, highlighted day will be after the 1st


            Set Found = .Range("G4:H4, B6:H6, B8:H8, B10:H10, B12:B12").Find(whatsToday)
            If Not Found Is Nothing Then Found.Interior.ColorIndex = 6
        Else    'if both fiscal and actual month are the same, highlighted day will be before the 1st


            If .Range("b4").Value <> 1 Then
                Set Found = .Range("B4:H4").Find(whatsToday)
                If Not Found Is Nothing Then Found.Interior.ColorIndex = 6
            End If
        End If
    End With

Dave

 

[LMjdm]

Dave,
Thanks this helped end the error. Only problem at this point is that, it doesn't highlight the day 26. I will include a picture to show you what I mean. Also, if its simple enough could explain what caused the error? Thanks for being my savior!!!!

Basically, whats in yellow should be highlighted and whats black is the ranges it should search to find it.

 

[dmt32]

Try adding some settings to the Find method:

If actualMonth = currentFiscalMonth Then    'if both fiscal and actual month are the same, highlighted day will be after the 1st


            Set Found = .Range("G4:H4, B6:H6, B8:H8, B10:H10, B12:B12").Find(what:=whatsToday, LookIn:=xlValues, lookat:=xlWhole)
            If Not Found Is Nothing Then Found.Interior.ColorIndex = 6
        Else    'if both fiscal and actual month are the same, highlighted day will be before the 1st


            If .Range("b4").Value <> 1 Then
                Set Found = .Range("B4:H4").Find(what:=whatsToday, LookIn:=xlValues, lookat:=xlWhole)
                If Not Found Is Nothing Then Found.Interior.ColorIndex = 6
            End If
        End If

Dave

 

[LMjdm]

Sorry dave, that doesn't seem to work. Just an FYI. in the picture I gave you, the date that should be highlighted is the 26th not 27th, doubt that makes any difference, just wanted to clarify. Thanks again for your effort!

 

[dmt32]

Is the value in the cell just 26 or is it a date (26/6/2014) formatted to display as day 26?

Dave

 

[LMjdm]

The exact function I use in L2 is "=DAY(I1)"

 

[dmt32]

sorry, I meant Cell E12 - is that just a number 26 or is it a Date (26/6/2014) Formatted as Day 26?

 

[LMjdm]

Is it simply the value "26"