Excel VBA "Object variable or With block variable not set "

Question

I have the following code:

Sub CheckDates()

bAlarm = False

i = 1
Do Until ActiveSheet.Cells(row_header, i).Value = ""
    If ActiveSheet.Cells(row_header, i).Value = searchText Then
        col = i
        Exit Do
    End If
i = i + 1
Loop

i = 1
Do Until ActiveSheet.Cells(row_header, i).Value = ""
    If ActiveSheet.Cells(row_header, i).Value = searchNameText Then
        col_name = i
        Exit Do
    End If
i = i + 1
Loop

If col = 0 Then
    MsgBox searchText & " basliklar arasinda bulunamadi"
    Exit Sub
Else
    N = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For i = row_header + 1 To N
        If isInDateRange(ActiveSheet.Cells(i, col).Value) Then
            With UserForm1.ListBox1
                .AddItem
                .List(.ListCount - 1, 0) = ActiveSheet.Cells(i, col_name).Value
                .List(.ListCount - 1, 1) = ActiveSheet.Cells(i, col).Value
            End With
            bAlarm = True
        End If
    Next i
End If

If bAlarm = True Then
    UserForm1.Show
End If

I am getting the Object variable or With block variable not set (Error 91) error on the line with Do Until ActiveSheet.Cells(row_header, i).Value = "". When I debug I can see that hprlink.Range does have a value. Any thoughts what I'm doing wrong?

CLR · Accepted Answer · 2017-04-06 08:56:41Z

1

Unless there is code missing from your question I think the issue is indeed in:

Do Until ActiveSheet.Cells(row_header, i).Value = ""

As far as I can tell, row_header is empty at this point, so will likely be resolving to:

Do Until ActiveSheet.Cells( 0, 1).Value = ""

There is no row 0, so you get your error.

Put a value in row_header (perhaps 1 - I can't see your sheet?) before you start your loops to locate the field.

Furthermore, you might want to check out WorksheetFunction.Match as it will do the same thing your first two loops are doing with just one line.

Finally, you probably should stop referring to ActiveSheet all the time throughout your sub, either use a With block or Set an object at the start to refer to.

edited Apr 6, 2017 at 8:56

answered Apr 6, 2017 at 8:49

CLR

12.3k1 gold badge13 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA "Object variable or With block variable not set "

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related