Object doesn't support this property or method when I try to run countif based on activex textb-CodePudding

I am relatively new to this coding. I am trying to add to my inventory database(in another sheet) if the model that is key into the activex textbox dose not match. If it matches, then it will automatically update to the quantity. However, I am getting error438. Here is the code that I have written so far.

Sub Add()

Dim invdata As Worksheet
Dim frm As Worksheet

Dim iqty As Integer

Set frm = ThisWorkbook.Sheets("UserForm")
Set invdata = ThisWorkbook.Sheets("Inventory Database")

iqty = frm.Range("B9")

Dim irow As Integer
Dim jrow As Integer 
Dim i As Integer

If Application.WorksheetFunction.CountIf(invdata.Range("C:C"), ActiveSheet.tbModel.Value) > 0 Then

    jrow = invdata.Range("A" & invdata.Rows.Count).End(xlUp).row   1
        With invdata
            .Cells(jrow, 1).Value = frm.Range("B6").Value
            .Cells(jrow, 2).Value = frm.Range("B7").Value
            .Cells(jrow, 3).Value = ActiveSheet.tbModel.Value
            .Cells(jrow, 4).Value = frm.Range("B9").Value
        End With
                MsgBox ("New Model Added!")

Else
    irow = invdata.Cells(Rows.Count, 3).End(xlUp).row

        For i = 2 To irow
            If Sheet1.Cells(i, 3) = ActiveSheet.tbModel.Value Then
                Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4)   iqty
                    Exit Sub
                End If
            Next i
    
                
End If

End Sub

CodePudding user response：

Try this - using Find() instead of CountIf() saves you from the loop:

Sub Add()

    Dim invdata As Worksheet, frm As Worksheet, model, f As Range
    Dim iqty As Long
    
    Set frm = ThisWorkbook.Sheets("UserForm")
    Set invdata = ThisWorkbook.Sheets("Inventory Database")
    
    iqty = frm.Range("B9").Value
    model = frm.OLEObjects("tbModel").Object.Value '####
    
    'see if there's a match using `Find()`
    Set f = invdata.Range("C:C").Find(what:=model, lookat:=xlWhole)
    If f Is Nothing Then
        'model was not found in Col C
        With invdata.Range("A" & invdata.Rows.Count).End(xlUp).Offset(1)
            .Value = frm.Range("B6").Value
            .Offset(0, 1).Value = frm.Range("B7").Value
            .Offset(0, 2).Value = model
            .Offset(0, 3).Value = iqty
        End With
        MsgBox "New Model Added!"
    Else
        With f.EntireRow.Cells(4)
            .Value = .Value   iqty '  update qty in row `m`
        End With
    End If

End Sub