runtime 1004 Application.WorksheetFunction.Match Unable to get the Match Property of the Work-CodePudding

here I tried to create some userforms to update my data table. I have this data table (CTR Summary information) to give a basic information because I have a lot of columns to fill and it will be kind of messy to put it all informations together in one form. So I split it to some section of form depend on the section need to be update.

If I click to update commandbutton it will redirect to Update form and it pull the data information section I need from the list box. Like the picture below

After I change my value from text box and click update, it gave me error Application.WorksheetFunction.Match Unable to get the Match Property of the Work. here's my code

Private Sub UPDATE_Click()

    If MsgBox(" Check the Data Again ", vbYesNo, "Proceed to Subcontractor Form Information if Any") = vbYes Then
        ' write the data to the worksheet from controls
        Call WriteDataToTheSheet
        
        ' empty the textboxes
        Call EmptyTextBoxes
    Else
    
        ' empty the textboxes
        Call EmptyTextBoxes
                
    End If 
End Sub

'UPDATE CODE
Private Sub WriteDataToTheSheet()

    
    With Sheet2
        Dim selectedRow As Long
        LRow = .Range("A" & .Rows.Count).End(xlUp).row
        selectedRow = Application.WorksheetFunction.Match(WONUMBER.Value, Sheet2.Range("I2:I" & LRow), 0)
        
        .Cells(selectedRow, 1).Value = Now
        .Cells(selectedRow, 2).Value = NAMACLIENT.Value
        .Cells(selectedRow, 3).Value = BLANKETNUMBER.Value
        .Cells(selectedRow, 4).Value = CTRNUMBER.Value
        .Cells(selectedRow, 5).Value = PICBIRU.Value
        .Cells(selectedRow, 6).Value = PICCLIENT.Value
        .Cells(selectedRow, 7).Value = PROJECTSTATUS.Value
        .Cells(selectedRow, 8).Value = PROJECTTITLE.Value
        .Cells(selectedRow, 9).Value = WONUMBER.Value
        .Cells(selectedRow, 10).Value = WODIR.Value
        .Cells(selectedRow, 11).Value = WOSTARTDATE.Value
        .Cells(selectedRow, 12).Value = WOENDDATE.Value
        .Cells(selectedRow, 13).Value = REMARKS.Value
        .Cells(selectedRow, 14).Value = WAPU.Value
        .Cells(selectedRow, 17).Value = ENGVALUE.Value
        .Cells(selectedRow, 18).Value = REIMBURSABLE.Value

    End With

End Sub

Can you tell me what I am doing wrong? I just started a couple weeks ago in vba and I make this code from different kind of youtube references. I just really stuck in this one. I'm really appreciate your help.

EDIT: sorry to gave you minimal information this is my excel data image

This is my debugging code image

CodePudding user response：

On the worksheet are the WONUMBERS numeric? If they are you would need to convert the text WONUMBER from the textbox in the userform to numeric, you can do that using Val.

selectedRow = Application.Match(Val(WONUMBER.Value), Sheet2.Range("I2:I" & LRow), 0)

Alternatively, you could add a, hidden, textbox on the update form, populate it with the row the selected in the previous form and use that when updating.

CodePudding user response：

Alternatively, you can put your search value into a cell on some worksheet and use that cell in the match statement. That worksheet can be hidden.