New to VBA. Been searching for answer for two days without finding a question that really asks what I want an answer to.
I am working with a UserForm that populates a Table in one of my sheets.
My problem is within the Sub UserForm_Initialize(). One of the first things I'm trying to do is to find out whether or not my last row (and incidentally, first column) in my DataBodyRange of said table contains an ID-number.
If it does, then I take that value, add one, and populate a textbox inside my UserForm.
If, however, the table consists of the headers and one empty row I want to populate the TextBox with the number 1 (that through another sub will be added to this empty row), but my code stops working with an error.
With the following code I get the error
Run-Time Error '91': Object Variable or With Block Variable Not Set
Private Sub UserForm_Initialize()
Dim tbl As ListObject, rng As Range
Set tbl = Worksheets("Sheet1").ListObjects("table1")
Set rng = tbl.ListColumns("ID").DataBodyRange
If IsEmpty(rng.Cells.Find("*", LookIn:=xlValues,_
SearchOrder:=xlByRows,_
SearchDirection:=xlPrevious).Value) Then
Me.textBox.Value = 1
Else
Me.textBox.Value = rng.Cells.Find("*", LookIn:=xlValues,_
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Value + 1
End If
End Sub
I don't understand the explanations I find regarding the error code or how to fix it. I gather that the fault has something to do with the rng.Cells.Find... because that's when the error shows up when stepping through the code, but I can for the life of me not understand why.
Find() returns a Range, and if nothing is found, then the Range is Nothing. Thus, it should be checked against Is Nothing.
In the code below, a new range variable, result is introduced. It is checked for being nothing:
Private Sub UserForm_Initialize()
Dim tbl As ListObject, rng As Range
Set tbl = Worksheets("Sheet1").ListObjects("table1")
Set rng = tbl.ListColumns("ID").DataBodyRange
Dim result As Range
Set result = rng.Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not result Is Nothing Then
Debug.Print result.Address
Else
Debug.Print "result is nothing"
End If
End Sub