Excel数据检查 [英] Excel data checking

问题描述

我有一个Excel用户表单，其中包含以下字段； 日期，名称和工作。

I have an Excel UserForm which contains the following fields; date, name and work.

如果工作表中包含相同的日期和名称配对，我希望阻止从用户表单进行的数据传输。

In the event that the worksheet contains the same date and name pairing, I want data transfer from the Userform blocked.

一个工作表有一个条目：

A worksheet has one entry:

• 列 A 具有日期 1/1/2017


• 列 B 的名称为 john

• Column A has the date 1/1/2017
• Column B has the name john

在上面的工作表中，适用以下规则：

For the worksheet above, the following rules apply:

1. 可以在 2017/3/1的用户窗体中传递信息和 john 。


2. 信息可以从用户窗体传递，用于 1 / 1/2017 和 jane 。


3. 无法从用户窗体传递 1/1/2017 和 john 。

1. Information can be passed from the UserForm for 3/1/2017 and john.
2. Information can be passed from the UserForm for 1/1/2017 and jane.
3. Information cannot be passed from the UserForm for 1/1/2017 and john.

推荐答案

Dim k As Long
Dim matched As Boolean
matched = False

' Loop over all used rows
For k = 1 to ActiveSheet.UsedRange.Rows.Count

    ' Check if concatenated string of date & name is unique
    ' e.g. '01/01/17john'
    If ActiveSheet.Cells(k, "A").Text & ActiveSheet.Cells(k, "B").Text = _
       myUserForm.DateField.Text & myUserForm.NameField.Text Then

        MsgBox "This Name / Date combination is not unique, pick again"

        matched = True

        Exit For

    End If

Next k

If matched = False Then

    ' Name/Date combination is unique, send data to sub or whatever... 

End If

这篇关于Excel数据检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！