时间戳

首页 > 解决方案 > vba 循环不检查重复的零件号

excel - vba 循环不检查重复的零件号

问题描述

我需要我的循环来检查现有的零件编号，并且只有在没有现有零件编号的情况下才能将其添加到我的表中。如果部件号已经存在，有一个消息框说明它已经存在。它将它添加到我的表中就好了，但如果已经有一个现有的部件号，它不会给我消息框。

Private Sub Add_Click()

Dim ws As Worksheet
Set ws = Sheet4
Dim X As Integer
Dim lastrow As Long
Dim PartColumnIndex As Integer
Dim DescriptionColumnIndex As Integer

Const Part = "CM ECP"
Const Description = "Material Description"

Dim PartNum As String
Dim MaterailDescription As String

Dim tbl As ListObject

Set tbl = ws.ListObjects("Master")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add

With ws
    On Error Resume Next
    Let PartColumnIndex = WorksheetFunction.Match(PartNum, .Rows(2), 0)
    Let DescriptionColumnIndex = WorksheetFunction.Match(MaterialDecription, .Rows(2), 0)
    Let lastrow = .Cells(.Rows.Count, PartColumnIndex).End(xlUp).Row
    
    X = 3
    
    Do
        Let PartValue = .Cells(X, PartColumnIndex).Value
        Let DecriptionColumnIndex = .Cells(X, DecriptionColumnIndex).Value
        If TextBox1.Value = PartValue Then
            MsgBox "Part Number " + TextBox1.Value + " already exists. Please try again or return to main screen."
        ElseIf TextBox1.Value <> PartValue Then
            With newrow
                .Range(1) = TextBox1.Value
                .Range(2) = TextBox2.Value
            End With
        ElseIf X < lastrow Then
            X = X + 1
        
            
        End If
                 
        
    
    Loop Until X > lastrow

    End With

标签： excelvbado-loops

解决方案

在决定是否添加新行之前扫描表中的所有行，并始终将 Use Option Explicit 添加到代码顶部以捕获诸如DecriptionColumnIndex(no s) 之类的错误。

Option Explicit

Sub Add_Click()

    Const PART = "CM ECP"
    Const DESCRIPTION = "Material Description"

    Dim ws As Worksheet
    Dim X As Integer, lastrow As Long
    Dim PartColumnIndex As Integer, DescrColumnIndex As Integer
    Dim PartNum As String, MaterialDescription As String
    Dim tbl As ListObject, bExists As Boolean
    
    Set ws = Sheet1
    Set tbl = ws.ListObjects("Master")
    With tbl
        
        PartColumnIndex = .ListColumns(PART).Index
        DescrColumnIndex = .ListColumns(DESCRIPTION).Index
        
        PartNum = Trim(TextBox1.Value)
        MaterialDescription = Trim(TextBox2.Value)
        
        ' search
        With .DataBodyRange
            lastrow = .Rows.Count
            For X = 1 To lastrow
                If .Cells(X, PartColumnIndex).Value = PartNum Then
                    bExists = True
                    Exit For
                End If
            Next
        End With
        
        ' result
        If bExists = True Then
            MsgBox "Part Number `" & PartNum & "` already exists on Row " & X & vbLf & _
            "Please try again or return to main screen.", vbExclamation
        Else
            With .ListRows.Add
                .Range(, PartColumnIndex) = PartNum
                .Range(, DescrColumnIndex) = MaterialDescription
            End With
            MsgBox "Part Number `" & PartNum & "` added", vbInformation
        End If
        
    End With
End Sub

推荐阅读