CODEWITHSUNDEEP
excelvba
A.Alt
A.Alt

Reputation: 13

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

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?

Upvotes: 1

Views: 78

Answers (1)

CLR
CLR

Reputation: 12279

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.

Upvotes: 1

Related Questions