Help with this VB code

skeeeter56

New Member
Joined
Nov 26, 2016
Messages
42
Office Version
  1. 2019
Platform
  1. Windows
The below code works perfect taking what is in the form and adds it to a table on another sheet.

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
I am hoping someone can assist with what is going wrong. I have checked the sheet name and table name which are correct
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Is your table name "table2" or is it "Table2"?
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top