Getting a bug, then excel crashes on "Cells(iRow2, 1).Value = cmbCompany.Value"

[MadCow]

Hi All,
I'm fairly new to VBA, and have been struggling with this code. When I input a NEW [Company] in my form I want the name to be added the bottom of the 'Customers' table. A single column table @ Data!A2:A12
Everything works great until it tries to actually write the value into the cell. When it tries, I get "Method 'Value' of object 'Range' failed". and when I either End or Debug excel crashes.

If I choose an existing [Company] in my form and just add new contact info then I have no issue.

Code where I get the bug

    'Check to see if the Company already exists, if it does NOT, add it to the company table
       
        Set r = wsData.ListObjects("Customers").Range
        Set rngCompany = r.Find(strCompany, LookIn:=xlValues)
        If rngCompany Is Nothing Then
            iRow2 = wsData.ListObjects("Customers").Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
            Cells(iRow2, 1).Value = cmbCompany.Value
        End If


End Sub

Full code

Private Sub cmdSubmit_Click()

'Add new customer to the customer table
Dim iRow, iRow2, iAnswer As Integer
Dim strContact, strCompany As String
Dim wsData As Worksheet
Dim rngCompany, r As Range
Set wsData = Worksheets("Data")

Application.ScreenUpdating = False

    Sheets("data").Activate
    'Find last row in the contacts table
    iRow = wsData.ListObjects("Contacts").Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1

    'Copy data from the form to the contacts table
    Cells(iRow, 3).Value = cmbCompany.Value
    Cells(iRow, 4).Value = txtContact.Value
    Cells(iRow, 5).Value = txtAddress.Value
    Cells(iRow, 6).Value = txtCity.Value
    Cells(iRow, 7).Value = txtState.Value
    Cells(iRow, 8).Value = txtZip.Value
    Cells(iRow, 9).Value = txtPhone.Value
    Cells(iRow, 10).Value = txtEmail1.Value
    Cells(iRow, 11).Value = txtEmail2.Value
 
    'Set some variables for later use
    strContact = Cells(iRow, 4).Value
    strCompany = Cells(iRow, 3).Value
 
    'Check to see if the Company already exists, if it does NOT, add it to the company table
     
        Set r = wsData.ListObjects("Customers").Range
        Set rngCompany = r.Find(strCompany, LookIn:=xlValues)
        If rngCompany Is Nothing Then
            iRow2 = wsData.ListObjects("Customers").Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
            [B]Cells(iRow2, 1).Value = cmbCompany.Value[/B]
        End If
         
    'Check with user to see if we want to add another contact
    iAnswer = MsgBox(strContact & " has been successfully added to " & strCompany & ".  Would you like to add another?", _
              vbQuestion + vbYesNo + vbDefaultButton2, "Success")
 
    If iAnswer = vbYes Then
        Call UserForm_Initialize
        GoTo ImDone
     
    Else
        Unload Me
    End If
 
    'Sort the contacts table
    wsData.ListObjects("Contacts").Sort.SortFields.Clear
    wsData.ListObjects("Contacts").Sort.SortFields.Add2 _
        Key:=Range("Contacts[[#All],[Company]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").ListObjects("Contacts").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    Sheets("Quotation").Activate
 
Application.ScreenUpdating = True

ImDone:

End Sub

 

[DanteAmor]

Try this:

Private Sub cmdSubmit_Click()
  'Add new customer to the customer table
  Dim iRow As Long, iRow2 As Long
  Dim wsData As Worksheet
  Dim rngCompany As Range
 
  Application.ScreenUpdating = False
  Set wsData = Worksheets("Data")
  'Add row in the contacts table
  With wsData.ListObjects("Contacts")
    .ListRows.Add AlwaysInsert:=True
    iRow = .DataBodyRange.Rows.Count
    'Copy data from the form to the contacts table
    .DataBodyRange(iRow, 3) = cmbCompany.Value
    .DataBodyRange(iRow, 4) = txtContact.Value
    .DataBodyRange(iRow, 5) = txtAddress.Value
    .DataBodyRange(iRow, 6) = txtCity.Value
    .DataBodyRange(iRow, 7) = txtState.Value
    .DataBodyRange(iRow, 8) = txtZip.Value
    .DataBodyRange(iRow, 9) = txtPhone.Value
    .DataBodyRange(iRow, 10) = txtEmail1.Value
    .DataBodyRange(iRow, 11) = txtEmail2.Value
  End With
 
  'Check to see if the Company already exists, if it does NOT, add it to the company table
  With wsData.ListObjects("Customers")
    Set rngCompany = .DataBodyRange.Find(cmbCompany.Value, , xlValues, xlWhole, , , False)
    If rngCompany Is Nothing Then
      .ListRows.Add AlwaysInsert:=True
      iRow2 = .DataBodyRange.Rows.Count
      .DataBodyRange(iRow2, 1).Value = cmbCompany.Value
      .DataBodyRange.Sort key1:=.DataBodyRange.Cells(1), order1:=xlAscending, Header:=xlYes
    End If
  End With
  'Check with user to see if we want to add another contact
  Application.ScreenUpdating = True
  If MsgBox(txtContact & " has been successfully added to " & cmbCompany & ". " & _
    "Would you like to add another?", vbQuestion + vbYesNo, "Success") = vbNo Then Unload Me
 
  Call UserForm_Initialize
End Sub

 

[MadCow]

Hi Dante,
Sorry for the delay, vacations 'n all. I tried your solution above and I am getting an error in the same place and Excel still crashes.

 .DataBodyRange(iRow2, 1).Value = cmbCompany.Value

I have tried deleting and recreating the sheet that data was on to no avail.

 

[DanteAmor]

You have formulas, errors in the cells.
My tests are successful.
You can upload your file to googledrive and share the file, copy the link and paste it here.