WorksheetFunction.match run time error '1004' with numbers but not text

First time poster and newbie/self-taught to vba. I’ve been reading through this forum and others as I build my program. Thank you for the help so far, and now I’m reaching out because I’m stuck.

My program edits part number data via userform connecting back to the worksheet where the data was found.

The error code reads:
“Method of ‘Match’ of object ‘WorksheetFunction’ failed” run time error ‘1004’

This only happens when the part number is numeric data like ‘5555555’.
If the part number is text data like ‘T555555’ or ‘555555T’ the program executes correctly with no errors.

I’ve searched the forum for similar issues and can’t find any answers. Maybe I’m not asking the right question?

Any help is appreciated.


The error producing code is below and the part number name is In_SubComPNmod. It’s pulled from a text box in the userform (I don’t want the PN to be modified) so it comes from the .caption.

Dim PartNumRow As String

'Make SubCom2 Active
Sheets("StartHereSubCom").Activate

'Determine PartNumRow

PartNumRow = Application.WorksheetFunction.Match(In_SubComPNmod.Caption, Worksheets("StartHereSubCom").Range("A1:A999"), 0)
MsgBox "Found Original PN at row : " & PartNumRow

'Export Data to worksheet
Cells(PartNumRow, 6).Value = In_SubComDescribe.Value
Cells(PartNumRow, 15).Value = In_Material2.Value
Cells(PartNumRow, 16).Value = In_Quantity2.Value