时间戳

首页 > 解决方案 > 在添加到工作表之前重复发现

excel - 在添加到工作表之前重复发现

问题描述

大家早

有人可以告诉我我哪里出错了吗？我有一个用户表单，它将数据添加到工作表但是我希望消息框显示 B 列中的名字和 C 列中的姓氏在添加到工作表之前是否已经存在。

If WorksheetFunction.CountIf(Sheet2.Range("B:C"), Me.textbox1.Value and Me.textbox2.Value) > 0 Then
        MsgBox "This staff member already exists"
        Exit Sub
    End If

标签： excelvba

解决方案

使用Find和FindNext

Option Explicit
Sub CheckExists()

    Dim rng As Range, firstFind As String, bExists As Boolean
    Dim firstname As String, surname As String
    firstname = Me.textbox1.Value
    surname = Me.textbox2.Value

    ' find in column B
    Set rng = Sheet1.Columns(2).Find(firstname, LookIn:=xlValues, lookat:=xlWhole)
    If rng Is Nothing Then
        ' not found
    Else
        firstFind = rng.Address
        Do
            If rng.Offset(0, 1) = surname Then ' Column C
                bExists = True
                Exit Do
            End If
            Set rng = Sheet1.Columns(2).FindNext(rng)
            
         Loop While Not rng Is Nothing And rng.Address <> firstFind
    End If

    If bExists Then
         MsgBox firstname & " " & surname & " already exists", vbExclamation, "Row " & rng.Row
         Exit Sub
    End If

End Sub

推荐阅读