时间戳

首页 > 解决方案 > 如何将此布尔变量实现到此脚本？

excel - 如何将此布尔变量实现到此脚本？

问题描述

我有这个脚本，它用另一张表原始数据填充一张表。它具有特定的嵌套 IF 和 Do While 循环，并且我被要求添加的最后一件事。

rFound.Offset(, 2).Value当用户输入工作代码时，我想知道是否可以仅在= 字符串的情况下在输入框中添加Exempt？如果该列中的值显示除 Exempt 以外的任何内容，则不会触发 msg 框。我知道这可能是一个布尔变量，但我不知道在哪里放置它？

我希望消息框显示“此员工是否豁免，他们应该......”

脚本的其余部分：（如果发生任何混淆，我可以评论行） Sub tgr()

Dim rFound As Range
Dim lJobCode As String
Dim lFLSA As String
Dim lEC As String
Dim sFirst As String
Dim sResults As String
Dim sh As Worksheet
Dim rw As Long
Dim matched As Boolean


lJobCode = Application.InputBox("Please provide a job code", "Job Code", Type:=2)
lEC = Application.InputBox("Please select EC Member", "EC Member", Type:=2)

If lJobCode = "False" Or lEC = "False" Then Exit Sub   'Pressed cancel

Set sh = Sheets("Sheet1")
rw = 8
With ThisWorkbook.Worksheets("Sheet2").Columns("A")
    Set rFound = .Find(lJobCode, .Cells(.Cells.Count), xlValues, xlWhole)

    If Not rFound Is Nothing Then
        sFirst = rFound.Address
        Do
            If ThisWorkbook.Worksheets("Sheet2").Cells(rFound.Row, 9).Value = lEC Then
                matched = True

                If rFound.Offset(, 2).Value = "Exempt" Then
                    MsgBox "Is this employee exempt, and do they work consistent hours after 8pm?"
                Else

                    sh.Cells(rw, 4) = rFound.Offset(, 0).Value
                    sh.Cells(rw, 5) = rFound.Offset(, 1).Value
                    sh.Cells(rw, 6) = rFound.Offset(, 3).Value
                    sh.Cells(rw, 7) = rFound.Offset(, 5).Value
                    sh.Cells(rw, 8) = rFound.Offset(, 6).Value
                    sh.Cells(rw, 9) = rFound.Offset(, 7).Value

                    rw = rw + 1
                End If
            End If
            Set rFound = .FindNext(rFound)
        Loop While rFound.Address <> sFirst

        If Not matched Then MsgBox "Job Code [" & lJobCode & "] found, but not eligible for EC Member"

    Else
        MsgBox "Job Code [" & lJobCode & "] not eligible."
    End If

End With

End Sub

标签： excelvba

解决方案

将建议作为答案发布，以便可以将其标记为已回答。

if rFound.Offset(, 2).Value = "Exempt" then userform1.load

或作为 msgbox()

if rFound.Offset(, 2).Value = "Exempt" then msgbox("content")

编辑1：

如果您希望在 match = true 时提取数据，但又希望弹出 msgbox，请尝试以下位置：

If ThisWorkbook.Worksheets("Sheet2").Cells(rFound.Row, 9).Value = lEC Then
    matched = True
    sh.Cells(rw, 4) = rFound.Offset(, 0).Value
    sh.Cells(rw, 5) = rFound.Offset(, 1).Value
    sh.Cells(rw, 6) = rFound.Offset(, 3).Value
    sh.Cells(rw, 7) = rFound.Offset(, 5).Value
    sh.Cells(rw, 8) = rFound.Offset(, 6).Value
    sh.Cells(rw, 9) = rFound.Offset(, 7).Value
    If rFound.Offset(, 2).Value = "Exempt" AND z = 0 Then 
        MsgBox "Is this employee exempt, and do they work consistent hours after 8pm?"
        z = 1
    End if
    rw = rw + 1
End If

推荐阅读