我有以下功能,将行添加到工作表。它会在添加零件之前检查零件号是否存在。不幸的是,Match没有找到现有数据,而是悄悄地添加了相同零件号的多个副本。
谁能指出我所缺少的吗?
Private Sub OkButton_Click()
Dim LastRow As Long
LastRow = LastRowOnSheet("Parts List")
Dim sht As Worksheet
Set sht = Worksheets("Parts List")
'Validate that the controls hold valid data
If Not (Me.PartNumberTextBox.Value Like "######") Then
MsgBox "Please enter a valid 6 digit Stackpole part number.", vbExclamation, "Invalid Part Number"
Me.PartNumberTextBox.SetFocus
Exit Sub
End If
If Me.DescriptionTextBox.Value = "" Then
MsgBox "Please enter a description for this part.", vbExclamation, "Description Required"
Me.DescriptionTextBox.SetFocus
Exit Sub
End If
'Validate that the part number does not already exist
On Error Resume Next
x = WorksheetFunction.Match(PartNumberTextBox.Value, sht.Range(sht.Cells(2, 3), sht.Cells(2, LastRow)), 0)
If Not (x = "") Then
x = x + 1
MsgBox ("Duplicate part number found at row: " & x)
Exit Sub
End If
'Add new row to the Parts List Sheet
With Worksheets("Parts List").Range("A1")
.Offset(LastRow, 0).Value = .Offset(LastRow - 1, 0).Value + 1
.Offset(LastRow, 1).Value = Me.DescriptionTextBox.Value
.Offset(LastRow, 2).Value = Me.PartNumberTextBox.Value
.Offset(LastRow, 3).Value = Me.StoresLocTextBox
End With
End Sub
我认为PartNumberTextBox.Value是一个字符串,但单元格值是整数。将其强制转换为Integer可能会解决问题
x = WorksheetFunction.Match(Int(PartNumberTextBox.Value), sht.Range(sht.Cells(2, 3), sht.Cells(2, LastRow)), 0)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句