Object variable or With block variable not set (Error 91) in excel

Question

Getting error: Object variable or With block variable not set (Error 91) in excel vba.

    Sub RegisterNewUser()

'Disable screen refresh during code excution
Application.ScreenUpdating = False


'Variables
Dim users As Worksheet           'Variable for users table sheet
Dim NewUser As Worksheet         'Variable for new user entry sheet
Dim RecordIsValid As String
Dim NextRecordIndex As Long      'Variable for the next available row in the users table

Set users = ThisWorkbook.Worksheets("USERS")
Set NewUser = ThisWorkbook.Worksheets("NEW USER")
NextRecordIndex = users.ListObjects("USERS").ListRows.Count

If NextRecordIndex = 0 Then

users.ListObjects("USERS").DataBodyRange(NextRecordIndex, 1).Value = NewUser.Range("D" & 6).Value

Else

NextRecordIndex = NextRecordIndex + 1

users.ListObjects("USERS").DataBodyRange(NextRecordIndex, 1).Value = NewUser.Range("D" & 6).Value

    End If

Error is exactly here:

after the if statement:

users.ListObjects("USERS").DataBodyRange(NextRecordIndex, 1).Value = NewUser.Range("D" & 6).Value

The code in the else condition is working fine. It only gives this error if the USERS table is empty and the row count is zero.

Can anyone please guide what is the problem?

Thanks..

Check if ThisWorkbook.Worksheets("NEW USER") evaluates to Nothing — AnalystCave.com
– AnalystCave.com, Commented May 2, 2015 at 14:00
The code in else condition is working fine: NextRecordIndex = NextRecordIndex + 1 users.ListObjects("USERS").DataBodyRange(NextRecordIndex, 1).Value = NewUser.Range("D" & 6).Value It only doesn't work if NextRecordIndex is 0 (Table is empty) — Superman Akrt
– Superman Akrt, Commented May 2, 2015 at 14:08

BrakNicku · Accepted Answer · 2015-05-02 14:27:43Z

3

If the number or records is 0, you can't refer to:

users.ListObjects("USERS").DataBodyRange

Because it does not exist (DataBodyRange = Nothing)

You should add an empty row first:

users.ListObjects("USERS").ListRows.Add

You should also replace:

users.ListObjects("USERS").DataBodyRange(NextRecordIndex, 1).Value

with

users.ListObjects("USERS").DataBodyRange(NextRecordIndex+1, 1).Value

EDIT:

The if statement is probably redundant, the following code should work in every case:

NextRecordIndex = users.ListObjects("USERS").ListRows.Count

users.ListObjects("USERS").ListRows.Add
users.ListObjects("USERS").DataBodyRange(NextRecordIndex + 1, 1).Value = 
      NewUser.Range("D" & 6).Value

edited May 2, 2015 at 14:27

answered May 2, 2015 at 14:08

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

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

4 Comments

Superman Akrt Over a year ago

Nice hint. error is gone but the records got inserted in the header row. Any way to insert in the first table row when table is empty?

BrakNicku Over a year ago

@SupermanAkrt Please take a look at the second part of the answer (edit). Simply add 1 to NextRecordIndex.

Superman Akrt Over a year ago

Great, working now but I have a question: When I user: users.ListObjects("USERS").DataBodyRange(1, 1).Value It added in the 2nd DataBodyRange row.

BrakNicku Over a year ago

@SupermanAkrt I have edited my answer once again to simplify your code.

Collectives™ on Stack Overflow

Object variable or With block variable not set (Error 91) in excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related