Object Variable or With Block Not Set (Excel VBA)

Question

Just in case it helps - I have had no problems with this macro for more than a year until mysteriously I woke up this morning and boom, it's falling over citing "Run-time error '91': Object Variable or With Block Not Set"(office update perhaps?).

This part of my code looks for today's date in each worksheet and takes the value of the cell next to it to show in a userform (tbProg, tbPlan and tbImp are all textboxes)

    For Each Sh In ThisWorkbook.Worksheets

    If Sh.Name = "Progress since last meeting" Then
        tbProg.Text = Sh.Cells.Find(sNow).Offset(0, 1).Select
        tbProg.BackColor = Sh.Cells.Find(sNow).Offset(0, 2).Interior.Color
        x = x + 1
    End If
    If Sh.Name = "Planned before next meeting" Then
        tbPlan.Text = Sh.Cells.Find(sNow).Offset(0, 1).Value
        x = x + 1
    End If
    If Sh.Name = "Impediments" Then
        tbImp.Text = Sh.Cells.Find(sNow).Offset(0, 1).Value
        x = x + 1
    End If
    Next

The error occurs on the line

tbProg.Text = Sh.Cells.Find(sNow).Offset(0, 1).Select

After a bit of trial and error, everything's groovy until I use

.Offset

or

.Select

or both, or any method at all.

Can anyone help me find a solution to this?

@Mikku same thing. I've so far tried .Value, .Select and .Text and all result in the same error. — The Benetrator
– The Benetrator, Commented Jun 10, 2019 at 10:04

FAB · Accepted Answer · 2019-06-10 10:36:31Z

1

I think what happens in your case is that you don't really find that sNow value, your code assumes that it should be there each time.

Also you don't need to use Find each time in that particular code, just once... try something like this:

    Dim rngNow As Range
    For Each Sh In ThisWorkbook.Worksheets
        Set rngNow = Sh.Cells.Find(CDate(sNow))
        If rngNow is Nothing then Set rngNow = Sh.Cells.Find(sNow) 'Make a second attempt to find the date, in case is stored as a string instead.

        If Not rngNow Is Nothing Then

            If Sh.Name = "Progress since last meeting" Then
                tbProg.Text = rngNow.Offset(0, 1).Value
                tbProg.BackColor = rngNow.Offset(0, 2).Interior.Color
                X = X + 1
            End If
            If Sh.Name = "Planned before next meeting" Then
                tbPlan.Text = rngNow.Offset(0, 1).Value
                X = X + 1
            End If
            If Sh.Name = "Impediments" Then
                tbImp.Text = rngNow.Offset(0, 1).Value
                X = X + 1
            End If
        Else
            Debug.Print sNow & " was not found in " & Sh.Name
        End If
    Next Sh

edited Jun 10, 2019 at 10:36

answered Jun 10, 2019 at 10:01

FAB

2,5791 gold badge12 silver badges22 bronze badges

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

7 Comments

The Benetrator Over a year ago

Thank you @FAB - this has worked. Any final thoughts on why today it fell over after working for so long?

FAB Over a year ago

No problem, glad it works. Assuming until today you always had sNow value you were looking for there, so it didn't fail in getting the range.

FAB Over a year ago

@TheBenetrator see updated answer... the date you have in the sheet is a number, you are trying to find a string... hence not matching. Keep in mind that if at any point in the spreadsheet the date will be stored as a string instead, this will fail as well.

FAB Over a year ago

@TheBenetrator I've added one more line in the code (see updated again), make an extra attempt to find the date as a string, in case is not being located as a date. There might be better ways to code this, but should do in your case i think.

The Benetrator Over a year ago

Yep - that's made it more thorough. There were areas where the date being found was being treated as a string and other areas where it is being treated as a number - I have no idea why. Your updated answer caters for both and my code works again - thanks once more for you help

|

Collectives™ on Stack Overflow

Object Variable or With Block Not Set (Excel VBA)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related