CODEWITHSUNDEEP
excelvba
The Benetrator
The Benetrator

Reputation: 367

Object Variable or With Block Not Set (Excel VBA)

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?

Upvotes: 1

Views: 150

Answers (1)

FAB
FAB

Reputation: 2569

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

Upvotes: 1

Related Questions