Excel VBA - trying to find a date in a row of dates, get "object variable or with block variable not set" error

Question

Here's the summary of what I'm trying to do: I have two files, "yyy" is an extract from a system keeping track of sicknesses and holidays, "xxx" file is where we manually note down those things, so I'm trying to make a macro that would cross check the two and highlight consistency/inconsistency in the extract.

Problem: When I run the macro, I get an error "object variable or with block variable not set" when it gets to the following

Set y = Range(Workbooks("xxx.xlsm").Sheets("xxx ").Range("J2:NZ2").Find(dates.Value).Address) I tried to swap between the dates.value and dateStr as reference for what to find, but get the same error.

PS. I checked all the ranges over and over and over, everything seem to be correct. PPS. I probably have some extra defined bits, which aren't present in the code otherwise, apologies for the untidy code. I'm trying to mover past this error to finish the rest of it...

Many thanks in advance!

Option Explicit

Sub sickhol_check()

Dim names, dates, schedDates, n, schedName, x, y As Range

Dim wb, WBschedule As Workbook
Dim ws, WSschedule As Worksheet
Dim nameStr As String
Dim dateStr As Long

Set wb = Workbooks("yyy.xlsm")
Set ws = Sheets("Sheet1")
Set WBschedule = Workbooks("xxx.xlsm")
Set WSschedule = WBschedule.Sheets("xxx")
Set names = ThisWorkbook.Sheets("Sheet1").Range("D2:D1882")      ‘yyy workbook
Set schedName = Workbooks("xxx.xlsm").Sheets("xxx ").Range("F4:F300")
Set schedDates = Workbooks("xxx.xlsm").Sheets("xxx ").Range("J2:NZ2")

For Each n In names       ‘working in yyy workbook
If n.Value <> "" Then
    Set dates = ThisWorkbook.Sheets("Sheet1").Range(n.Offset(0, -3).Address)
    nameStr = n.Value
    dateStr = dates.Value
    If n.Value = schedName.Find(n.Value) Then
        Set x = Range(Workbooks("xxx.xlsm").Sheets("xxx ").Range("F4:F300").Find(nameStr).Address)
        Set y = Range(Workbooks("xxx.xlsm").Sheets("xxx ").Range("J2:NZ2").Find(dates.Value).Address)
        If n.Offset(0, 11).Value = "Sick Leave" Then
            If Workbooks("xxx.xlsm").Sheets("xxx ").Cells(x.Row, y.Column).Value = "Sick" Or Workbooks("xxx.xlsm").Sheets("xxx ").Cells(x.Row, y.Column).Value = "Sick - pending" Or Workbooks("xxx.xlsm").Sheets("xxx").Cells(x.Row, y.Column).Value = "Half-day Sick" Then
            With n
            .Interior.Color = RGB(0, 255, 0)
            End With
            Else:
            With n
            .Interior.Color = RGB(255, 0, 0)
            End With
            GoTo a:
            End If
        ElseIf n.Offset(0, 11).Value = "Holiday Annual Leave" Then
            If Workbooks("xxx.xlsm").Sheets("xxx").Cells(x.Row, y.Column).Value = "Hol. Approved" Or Workbooks("xxx.xlsm").Sheets("xxx").Cells(x.Row, y.Column).Value = "Half-day Hol." Then
            With n
            .Interior.Color = RGB(0, 255, 0)
            End With
            Else:
            With n
            .Interior.Color = RGB(255, 0, 0)
            End With
            GoTo a:
            End If
        ElseIf n.Offset(0, 11).Value = "Leave  - Unpaid" Then
            If Workbooks("xxx.xlsm").Sheets("xxx").Cells(x.Row, y.Column).Value = "TimeOff Approv." Or Workbooks("xxx.xlsm").Sheets("xxx").Cells(x.Row, y.Column).Value = "Unavailable" Then
            With n
            .Interior.Color = RGB(0, 255, 0)
            End With
            Else:
            With n
            .Interior.Color = RGB(255, 0, 0)
            End With
            GoTo a:
            End If
        End If
        MsgBox x.Address & " " & y.Address
        Else: GoTo a:
    End If
    Else: GoTo a:
End If
a: Next

Workbooks("xxx.xlsm") is not correct.... a workbook name is not the same as a filename. — braX
– braX, Commented Aug 9, 2017 at 11:04
if I remove a .xlsm format, I get the "subject is out of range" error. I have both files open as well... — Serge
– Serge, Commented Aug 9, 2017 at 11:09
Not sure why you are using Workbooks("xxx.xlsm").Sheets... when you have already qualified object: WBschedule. I would just use WBschedule.Sheets.... Also, is Workbooks("xxx.xlsm") already open? — Zac
– Zac, Commented Aug 9, 2017 at 11:11
There's a fair amount wrong with the code: you need to declare the data type of each variable - y is a range, but the other variables on that line are variants. Same with wb and ws. Set ws=Sheets("Sheet1") - is that meant to refer to the currently activeworkbook as you've not said which book it's in. Set dates - you're setting it to the address value of n - 3 columns, why not just set it to reference n-3 columns as both refs are on the same sheet: Set dates = n.Offset(, -3). Set x = Range... - does sheet xxx really have a space after it? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Aug 9, 2017 at 11:15
yeah, I started off using the wbschedule, but then had issues with that, and just switched to using the entire thing, as it's a very specific set of files and I first wanted to get the basic function working, and optimise later. and yeah, both files are open always — Serge
– Serge, Commented Aug 9, 2017 at 11:17

Darren Bartrup-Cook · Accepted Answer · 2017-08-09 11:22:45Z

1

For your specific problem (there are others in your code):

Set y = Range(Workbooks("xxx.xlsm").Sheets("xxx ").Range("J2:NZ2").Find(dates.Value).Address)

If dates.value doesn't exist in your sheet you'll get an Object variable or With block variable not set error.

This is because you're telling it to find the value and return it's address. You should try and find the value, check if it's found and then look at the address.

You also don't need to wrap it in a Range as the FIND function returns a range reference anyway.

Set y = Workbooks("xxx.xlsm").Sheets("xxx ").Range("J2:NZ2").Find(dates.Value)
If Not y Is Nothing Then
    MsgBox y.Address
    'Other code that relies on y.
End If

answered Aug 9, 2017 at 11:22

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Serge Over a year ago

Thanks for the tips, Darren. I actually updated the text somewhat to go around the particular issue, although it's technically still there. Lemme explain this a bit - it seems that the find function cannot locate the date value in the date range. I've triple checked that the date appears correctly in the date range. I've also tried defining the date as long and looking for that, but neither works for some odd reason.The date range is basically 01/01/2017 to 29/12/2017. When I debug the issue I can highlight the variables and see that the long or value is found correctly, but still no luck

Serge Over a year ago

ps. thanks a lot for sticking with my horrible explanation, I'm well aware of convoluted nature of my explanations, so apologies for that :)

Serge · Accepted Answer · 2017-08-09 15:43:29Z

0

So, I just decided to change the range.find function to the "for each..." way and found the necessary cell this way. Still no idea why .find didn't work, but the issues is basically not present anymore. For reference: I changed the following

 Set y = Range(Workbooks("xxx.xlsm").Sheets("xxx ").Range("J2:NZ2").Find(dates.Value).Address)

to the following:

    For Each d In Workbooks("xxx.xlsm").Sheets("xxx").Range("J2:NZ2")
    If d = dates.Value Then
    Set y = d
    Else: GoTo b
    End If
b:         Next

Thanks everyone for good tips!

answered Aug 9, 2017 at 15:43

Serge

1

Collectives™ on Stack Overflow

Excel VBA - trying to find a date in a row of dates, get "object variable or with block variable not set" error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related