skeeeter56
New Member
- Joined
- Nov 26, 2016
- Messages
- 42
- Office Version
- 2019
- Platform
- Windows
The below code works perfect taking what is in the form and adds it to a table on another sheet.
I am working on another project that I want to do same thing but has more 15 fields in table.
But when click ok get a Runtime Error 91 Object variable or with block variable not set.
When you debug it halts on tbl.DataBodyRange(lrow2, 1).Value = cboEmployee.Value
I am hoping someone can assist with what is going wrong. I have checked the sheet name and table name which are correct
Code:
Private Sub bOkay2_Click()
Dim tbl As ListObject
Dim ws As Worksheet
Dim lrow As Range
Dim lrow2 As Long
Set tbl = Sheets("Missed Scan Summary").ListObjects("tblncidents")
If tbl.ListRows.Count > 0 Then
Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
For col = 1 To lrow.Columns.Count
If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
tbl.ListRows.Add
Exit For
End If
Next
End If
lrow2 = tbl.ListRows.Count
tbl.DataBodyRange(lrow2, 1).Value = txtDate.Value
tbl.DataBodyRange(lrow2, 2).Value = cboRound.Value
tbl.DataBodyRange(lrow2, 3).Value = cboEmployee.Value
tbl.DataBodyRange(lrow2, 4).Value = cboType.Value
tbl.DataBodyRange(lrow2, 5).Value = txtArticleNo.Value
tbl.DataBodyRange(lrow2, 6).Value = cboReason.Value
tbl.DataBodyRange(lrow2, 7).Value = cboTeamLeader.Value
Unload Me
End Sub
I am working on another project that I want to do same thing but has more 15 fields in table.
But when click ok get a Runtime Error 91 Object variable or with block variable not set.
When you debug it halts on tbl.DataBodyRange(lrow2, 1).Value = cboEmployee.Value
Code:
Private Sub bOkay2_Click()
Dim tbl As ListObject
Dim ws As Worksheet
Dim lrow As Range
Dim lrow2 As Long
Set tbl = Sheets("PRP").ListObjects("table2")
If tbl.ListRows.Count > 0 Then
Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
For col = 1 To lrow.Columns.Count
If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
tbl.ListRows.Add
Exit For
End If
Next
End If
lrow2 = tbl.ListRows.Count
tbl.DataBodyRange(lrow2, 1).Value = cboEmployee.Value
tbl.DataBodyRange(lrow2, 2).Value = txtDate.Value
tbl.DataBodyRange(lrow2, 3).Value = cboRoundNo.Value
tbl.DataBodyRange(lrow2, 4).Value = txtSeq.Value
tbl.DataBodyRange(lrow2, 5).Value = txtSeqt.Value
tbl.DataBodyRange(lrow2, 6).Value = txtPre.Value
tbl.DataBodyRange(lrow2, 7).Value = txtPret.Value
tbl.DataBodyRange(lrow2, 8).Value = txtHand.Value
tbl.DataBodyRange(lrow2, 9).Value = txtHandt.Value
tbl.DataBodyRange(lrow2, 10).Value = txtLl.Value
tbl.DataBodyRange(lrow2, 11).Value = txtLlt.Value
tbl.DataBodyRange(lrow2, 12).Value = txtSp.Value
tbl.DataBodyRange(lrow2, 13).Value = txtSpt.Value
tbl.DataBodyRange(lrow2, 14).Value = txtRedi.Value
tbl.DataBodyRange(lrow2, 15).Value = txtredit.Value
Unload Me
End Sub