VBA-添加多个条件:如果在单元格中输入单词#1，#2等，则显示消息框

本文介绍了VBA-添加多个条件:如果在单元格中输入单词#1，#2等，则显示消息框的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想在此代码中添加多个条件:

I'd like to add multiple criteria to this code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
        Const srcCol As String = "A"
    Const Criteria As String = "*high*"

    Dim rng As Range: Set rng = Intersect(Columns(srcCol), Target)
    If rng Is Nothing Then
        Exit Sub
    End If

    Application.EnableEvents = False

    Dim aRng As Range
    Dim cel As Range
    Dim foundCriteria As Boolean
    For Each aRng In rng.Areas
        For Each cel In aRng.Cells
            If LCase(cel.Value) Like LCase(Criteria) Then
                MsgBox ("Please check 2020 assessment")
                foundCriteria = True
                Exit For
            End If
        Next cel
        If foundCriteria Then
            Exit For
        End If
    Next aRng

    Application.EnableEvents = True

End Sub

在当前状态下，这是以这种方式工作的:如果列"A"的单元格包含单词"high"，警报弹出.我想添加更多条件:如果"A"列中的单元格为包含高"但是如果列"A"中的单元格是包含评论家"，请向我显示相同的警告框.我从行"Const Criteria As String =" high "开始，并尝试添加"And"，"Or"，"If"，&"._"，但是添加第二条标准似乎无济于事.有提示吗?

At the current state, this works in this way: if a cell of column "A" contains word "high", alert pop up.I would like to add more criteria: if cell in column "A" contains "high" but ALSO if a cell in column "A" contains "critic", show me the same alert box.I started from row "Const Criteria As String = "high", and tried adding "And", "Or", "If", "& _", but nothing seems working to add the second criteria.Any hint?

工作表更改:目标包含多个字符串之一

如果计划针对各种条件专门使用 contains ，则可以进行以下更改:

A Worksheet Change: Target Contains One of Multiple Strings

If you plan on using exclusively contains for the various criteria, you can do the following changes:

Const CriteriaList As String = "high,critic" ' add more

If LCase(cel.Value) Like "*" & LCase(Criteria(n)) & "*" Then

代码

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Const srcCol As String = "A"
    Const Delimiter As String = "," ' change if you need "," in the criterias
    Const CriteriaList As String = "*high*,*critic*" ' add more

    Dim rng As Range: Set rng = Intersect(Columns(srcCol), Target)
    If rng Is Nothing Then
        Exit Sub
    End If

    Dim Criteria() As String: Criteria = Split(CriteriaList, Delimiter)

    Application.EnableEvents = False

    Dim aRng As Range
    Dim cel As Range
    Dim n As Long
    Dim foundCriteria As Boolean
    For Each aRng In rng.Areas
        For Each cel In aRng.Cells
            For n = 0 To UBound(Criteria)
                If LCase(cel.Value) Like LCase(Criteria(n)) Then
                    MsgBox ("Please check 2020 assessment")
                    foundCriteria = True
                    Exit For
                End If
            Next n
        Next cel
        If foundCriteria Then
            Exit For
        End If
    Next aRng

    Application.EnableEvents = True

End Sub

这篇关于VBA-添加多个条件:如果在单元格中输入单词#1，#2等，则显示消息框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

2等