Read several threads and suggestions, still getting [Type mismatch (Error 13)]

[badox]

Option Explicit

Sub Reset()            <- F8 ERROR
  
    Dim iRow As Long
  
    iRow = [Counta (Database!A:A)] ' identifying the last row            <- F8 ERROR
  
    With frmForm
  
        .txtPVnr.Value = ""
        .txtDatum.Value = ""
        .txtVerdachte.Value = ""
        .txtBedrijf.Value = ""
      
        .cmbPost.Clear
        .cmbPost.AddItem "post1"
        .cmbPost.AddItem "post2"
        .cmbPost.AddItem "post3"
      
        .cmbLocatie.Clear
        .cmbLocatie.AddItem "locatie1"
        .cmbLocatie.AddItem "locatie2"
        .cmbLocatie.AddItem "locatie3"
      
        .optJa1.Value = False
        .optNee1.Value = False
      
        .cmbOvertreding.Clear
        .cmbOvertreding.AddItem "fout1"
        .cmbOvertreding.AddItem "fout2"
        .cmbOvertreding.AddItem "fout3"
              
        .txtGewicht.Value = ""
        .txtTelling.Value = ""
        .txtBoete.Value = ""
      
        .cmbBevinding.Clear
        .cmbBevinding.AddItem "overtreding1"
        .cmbBevinding.AddItem "overtreding2"
        .cmbBevinding.AddItem "overtreding3"
              
        .cmbOpslagplaats.Clear
        .cmbOpslagplaats.AddItem "opslag1"
        .cmbOpslagplaats.AddItem "opslag2"
        .cmbOpslagplaats.AddItem "opslag3"

        .cmbVerbalisant1.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
        .cmbVerbalisant2.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
        .cmbVerbalisant3.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
        .cmbVerbalisant4.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
      
        .optJa2.Value = False
        .optNee2.Value = False
      
        .txtHoofdkantoor.Value = ""
        .txtOM.Value = ""
        .txtOpmerking.Value = ""
      
        .lstDatabase.ColumnCount = 22
        .lstDatabase.ColumnHeads = True
      
        .lstDatabase.ColumnWidths = "30,30,30,60,60,50,50,30,75,40,40,40,75,75,75,75,75,75,30,40,40,150"
      
        If iRow > 1 Then
          
            .lstDatabase.RowSource = "Database!A2:U" & iRow
        Else
          
            .lstDatabase.RowSource = "Database!A2:U2"
        End If
      
    End With
  
End Sub

Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
  
    Set sh = ThisWorkbook.Sheets("Database") 'assign the worksheet name to the defined worksheet variable Sh
  
    iRow = [Counta (Database!A:A)] + 1
  
    With sh
  
        .Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 2) = frmForm.txtPVnr.Value
        .Cells(iRow, 3) = frmForm.txtDatum.Value
        .Cells(iRow, 4) = frmForm.txtVerdachte.Value
        .Cells(iRow, 5) = frmForm.txtBedrijf.Value
        .Cells(iRow, 6) = frmForm.cmbPost.Value
        .Cells(iRow, 7) = frmForm.cmbLocatie.Value
        .Cells(iRow, 8) = IIf(frmForm.optJa1.Value = True, "Ja", "Nee")
        .Cells(iRow, 9) = frmForm.cmbOvertreding.Value
        .Cells(iRow, 10) = frmForm.txtGewicht.Value
        .Cells(iRow, 11) = frmForm.txtTelling.Value
        .Cells(iRow, 12) = frmForm.txtBoete.Value
        .Cells(iRow, 13) = frmForm.cmbBevinding.Value
        .Cells(iRow, 14) = frmForm.cmbVerbalisant1.Value
        .Cells(iRow, 15) = frmForm.cmbVerbalisant2.Value
        .Cells(iRow, 16) = frmForm.cmbVerbalisant3.Value
        .Cells(iRow, 17) = frmForm.cmbVerbalisant4.Value
        .Cells(iRow, 18) = frmForm.cmbOpslagplaats.Value
        .Cells(iRow, 19) = IIf(frmForm.optJa2.Value = True, "Ja", "Nee")
        .Cells(iRow, 20) = frmForm.txtHoofdkantoor.Value
        .Cells(iRow, 21) = frmForm.txtOM.Value
        .Cells(iRow, 22) = frmForm.txtOpmerking.Value
      
        End With
      
End Sub


Public Sub Show_Form()
  
    frmForm.Show            <- F8 ERROR
      
End Sub

______________________________________
Option Explicit

Private Sub cmdReset_Click()
  
    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want ot reset the form?", vbYesNo + vbInformation, "Confirmation")
  
    If msgValue = vbNo Then Exit Sub
  
    Call Reset
  
End Sub

Private Sub cmdSubmit_Click()
  
    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want ot save the data?", vbYesNo + vbInformation, "Confirmation")
  
    If msgValue = vbNo Then Exit Sub
  
    Call Submit
    Call Reset
  
End Sub


Private Sub UserForm_Initialize()            <- F8 ERROR

    Call Reset            <- F8 ERROR
  
End Sub

 

[Candyman8019]

Since you know how to get the last line on the SearchData tab, and you have 1 header row, the result count would be:

Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row -1

 

[badox]

Since you know how to get the last line on the SearchData tab, and you have 1 header row, the result count would be:

Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row -1

... no that just removes one of the search results and still displays the total as being only 1

 

[Candyman8019]

Where are you trying to display the result count?

 

[badox]

Where are you trying to display the result count?

in the message that post that records were found.

If Application.CountA(iSearchRow) > 0 Then
        
            frmForm.lstDatabase.RowSource = "SearchData!A2:X" & iSearchRow
            
            'counts the number of rows non-empty Cells
            counter = counter + 1
            
            MsgBox "Records found." & counter 'Display total records found with search

 

[Candyman8019]

ah, okay. In that case change this:

           MsgBox "Records found."

to this

            MsgBox iSearchRow - 1 & " Records found."

 

[badox]

ah, okay. In that case change this:

           MsgBox "Records found."

to this

            MsgBox iSearchRow - 1 & " Records found."

Many thanks, this solved the problem entirely, just had to also change back, we're finally cooking with gas...

iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row -1

to

iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

 

[Candyman8019]

There’s no better way to cook!

 

[badox]

I just realized that this thread did deviate from the original error, I need to go and mark the response as solved.

I'm still learning and exploring the various capabilities of vba and trying to improve my understanding and application of it.

I thought it would be more practical to have the form insert a new row above the data submitted so that I always have the most recent data at the top, the code isn't returning any errors, however it's also not doing what I wanted it to.

Sub NewLine()
        
        On Error GoTo enditall
            Application.EnableEvents = False
            
            If Target.Cells.Column = 24 Then
                
                'If data is stored in row 2 move one row down
                If Target.Cells.Row = 2 Then
                    
                    N = Target.Cells.Row
                    If Range("X" & N).Value <> "" Then
                    
                    Range("X2").EntireRow.Insert
                    Range("A2").Select
                    
                End If
            End If
        End If
        
enditall:
        Application.EnableEvents = True
        
End Sub

 

[Candyman8019]

What sheet are you wanting to insert a row on?
It could be as simple as:

sheets(“searchdata”).range(“a2”).entirerow.insert

 

[badox]

What sheet are you wanting to insert a row on?
It could be as simple as:

sheets(“searchdata”).range(“a2”).entirerow.insert

on the database sheet,

The idea is to always have the most recent input data to the top of the Database

3. 03-01-2023
2. 02-01-2023
1. 01-01-2023