Data Entry Form Fails if selected row has missing data in the first column-CodePudding

I get a runtime error of 1004, unable to get the match property of the worksheet function class when I select an item from my list box to edit with a edit button.

the debug highlights the problem child. I realize that if the first column has data in it, it will work without issue, if it has no data, then it will fail.

I starred the line that is highlighted. I understand it is failing to match what is in the first column. Is there a way to make it run if there is any data in the row period? there are times there isn't data to input at first in the first column.

or should I make a hidden column with a auto incrementing number so the first column always has data?

Further details on what this is doing. I am assigning a value to a textbox so it can be used to identify what row I am actively working on. it does this by assigning the first column A:A to the txtRowNumber. If no item is selected from the listbox, the macro will just select the next empty row as shown below in the subroutine submit().

my goal is to be able to select the row regardless if there is data in the first column or not. I want to be able to continue with editing and saving any row selected

thank you!

Private Sub EditButton_Click()

    If selected_list = 0 Then
        MsgBox "No Row has been selected", vbkonly   vbInformation, "Edit"
        
        Exit Sub
            
    End If
    
    'code to update the value to respective controls
    
    *Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.LstDataBase.List(Me.LstDataBase.ListIndex, 0), ThisWorkbook.Sheets("Active").Range("A:A"), 0)*
    
    Me.SctaskInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 0)
    Me.TechInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 1)
    Me.CustomerInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 2)
    Me.SectionInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 3)
    
    Me.OldSNInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 4)
    Me.OldBTInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 5)
    Me.OldModelInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 6)
    
    Me.NewSNInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 7)
    Me.NewBTInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 8)
    Me.NewModelInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 9)
    Me.StatusInput.Value = Me.LstDataBase.List(Me.LstDataBase.ListIndex, 10)

End Sub
Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long
    
    Set sh = ThisWorkbook.Sheets("Active")
    
    If frmform.txtRowNumber.Value = "" Then
        iRow = [counta(Active!A:A)]   1
    Else
        iRow = frmform.txtRowNumber.Value
    End If
    
    With sh
    
    .Cells(iRow, 1) = frmform.SctaskInput.Value
    .Cells(iRow, 2) = frmform.TechInput.Value
    .Cells(iRow, 3) = frmform.CustomerInput.Value
    .Cells(iRow, 4) = frmform.SectionInput.Value
    .Cells(iRow, 5) = frmform.OldSNInput.Value
    .Cells(iRow, 6) = frmform.OldBTInput.Value
    .Cells(iRow, 7) = frmform.OldModelInput.Value
    .Cells(iRow, 8) = frmform.NewSNInput.Value
    .Cells(iRow, 9) = frmform.NewBTInput.Value
    .Cells(iRow, 10) = frmform.NewModelInput.Value
    .Cells(iRow, 11) = frmform.StatusInput.Value
    .Cells(iRow, 14) = IIf(frmform.YesOpt.Value = True, "Yes", "No")
    .Cells(iRow, 15) = [text(Now(), "YYYY/MM/DD HH:MM:SS")]
    .Cells(iRow, 16) = Application.UserName
    
    End With
End Sub

CodePudding user response：

Application.WorksheetFunction.Match will return a runtime error if the search value is not found.

The usual way to handle that is

Dim idx As Long
On Error Resume Next
idx = Application.WorksheetFunction.Match(Me.LstDataBase.List(Me.LstDataBase.ListIndex), ThisWorkbook.Sheets("Active").Range("A:A"), 0)
On Error GoTo 0
If idx = 0 Then
    ' value not found, now what?
Else
    Me.txtRowNumber.Value = idx
End If