Use of "Set" keyword not clear in VBA

Question

I am trying edit code in order to transfer data, that is to be entered into a worksheet, by using Excel's UserForm to actually enter the data. May sound convoluted. In other words type in the data in the UserForm, and upon clicking an inserted "command button" on the UserForm, the data will be placed on the next empty row of the Table.

The original code used set ws = DataTable, ws is Dim as a worksheet. While I know what a Table is, I'm not sure of what a DataTable is, or even if there is a difference between a DataTable and a Table of data. I fashion a Table of data with the first row as columns headings: Field 1, Field 2, and Field 3, and included 5 rows to be simple. I set ws = Table and Dim ws AS WorkSheet and Dim nextRow as Long. The UserForm will show, and the command button to close/cancel it works. However, when I enter the 3 pieces of data on the UserForm and click the command button to Enter/Save the data I get an error:

Run Time Error 91: Object varible or with block varible not set

If I choose "debug," the line of code that is highlighted in yellow is:

nextRow = ws.Cells(Rows.Count,1).End(xlUp).Offset(1,0).Row

I searched till I blue - am just learning VBA and would appreciate some help in interpreting what might be the issue. Most likely its the Table vs DataTable thing - not sure. Excels' DataForms are too inflexible to use for I need to use "Combo Boxes" to insure that only the correct options are selected from.

Thanks for suggestion or recommendations of other websites that may be beneficial to look over. Can anyone recommend a good on-line VBA class they've taken. Would prefer recommendations vs "shootin in the dark." Best week's wishes!!

[What does the keyword Set actually do in VBA?](http://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba) — Santosh, Jun 06 '13 at 19:02

John Alexiou · Answer 1 · 2013-06-06T20:00:37.953

Have you tried (pay attention to ws.Rows.Count)

Set nextRow = ws.Cells(ws.Rows.Count,1).End(xlUp).Offset(1,0).Row

without the worksheet clarification Rows returns the active sheet which may not be what you want.

BTW this is how I get the last row of a table. I have function for counting the rows with

Public Function CountRows(ByRef r As Range) As Integer
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
    End If
End Function

and then I use it as

Set r = ws.Range("A2")  'Or start of data
N = CountRows(r)
Set nextRow = r.Offset(N+1,0).Row

Use of "Set" keyword not clear in VBA

1 Answers1