CODEWITHSUNDEEP
excelvbamatch
cstrutton
cstrutton

Reputation: 6197

Workbook.Match is not finding the value it should

I have the following function that adds a row to a sheet. It checks to see if the Part Number exist before adding it in. Unfortunately Match is not finding the existing data and quietly adds multiple copies of the same Part Number.

Can anyone point out what I am missing?

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

Upvotes: 0

Views: 73

Answers (2)

Siphor
Siphor

Reputation: 2544

It searches the wrong range(row and column are reversed)

x = WorksheetFunction.Match(Int(PartNumberTextBox.Value), sht.Range(sht.Cells(2, 3), sht.Cells(LastRow, 3)), 0)

Upvotes: 1

Siphor
Siphor

Reputation: 2544

I think PartNumberTextBox.Value is a string but the cell values are integers. Casting it to Integer may solve the problem

x = WorksheetFunction.Match(Int(PartNumberTextBox.Value), sht.Range(sht.Cells(2, 3), sht.Cells(2, LastRow)), 0)

Upvotes: 1

Related Questions