Code giving Object Block Variable Error

Question

I have a small userform with one combo box and 2 text boxes and submit button.

While pressing the submit button, i am getting the object variable not set error.

Here is my code:

Private Sub CommandButton1_Click()

Dim ws As Worksheet, tbl As ListObject, row As ListRow

Set ws = Sheets("Create Account Heads")
Set tbl = ws.ListObjects(Me.TextBox2.Value)

Dim intValueToFind As String, rng As Range

Set rng = tbl.ListColumns(1).DataBodyRange    
intValueToFind = LCase(Me.TextBox3.Value)

If rng <> 0 Then
    For Each rng In rng
        If LCase(rng.Value) = intValueToFind Then
            MsgBox ("Account Head with this Name Already Exists.")
            Exit Sub
        End If
    Next rng
Else
    'Unprotect the Worksheet
    ws.Unprotect Password:="google"
End if

End Sub

i am getting the error in "If rng <> 0 Then" line.

Kindly review and advise to overcome this issue.

Thanks Salman

Indent your code, you have If rng <> 0 Then and Else , where is your End If ? — Shai Rado
try my code below, let me know if it works solved your error — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2016-11-20T08:56:10

0

votes

Replace your line code of:

If rng <> 0 Then

with:

If Not rng Is Nothing Then

Code giving Object Block Variable Error

1 Answers