I found the following sub for adding a new record with multiple fields to a table, but it keeps bombing out at the row that reads:
Set lastRow = table.ListRows(table.ListRows.Count).Range
With an error:
Run-time error '9':
Subscript out of range
The only modifications I made to it were:
I selected the sheet that has the table on it just after the Dim statements
In the next two lines I entered the name of the worksheet, then the name of the table.
I am passing to it a table name that is a string tblRecordLog, and a 5 item array called Values(), as in:
Call AddDataRow("tblRecordLog", Values())
Also I found if I didn't put the word "Call" in there it showed that line as an error (Which I don't understand either as I am used to not having to first type "Call"!).
Can anyone spot what is going wrong, let my know why, and how to fix?
Sub AddDataRow(tableName As String, Values() As Variant)
Dim sheet As Worksheet
Dim table As ListObject
Dim col As Integer
Dim lastRow As Range
Worksheets("RecordLog").Select
Set sheet = ActiveWorkbook.Worksheets("RecordLog")
Set table = sheet.ListObjects.Item("tblRecordLog")
'First check if the last row is empty; if not, add a row
If table.ListRows.Count > 0 Then
Set lastRow = table.ListRows(table.ListRows.Count).Range
For col = 1 To lastRow.Columns.Count
If Trim(CStr(lastRow.Cells(1, col).Value)) <> "" Then
table.ListRows.Add
Exit For
End If
Next col
End If
'Iterate through the last row and populate it with the entries from values()
Set lastRow = table.ListRows(table.ListRows.Count).Range
For col = 1 To lastRow.Columns.Count
If col <= UBound(Values) + 1 Then lastRow.Cells(1, col) = Values(col - 1)
Next col
End Sub
Still learning much by fire, appreciate the help immensely.
Bookmarks