时间戳

首页 > 解决方案 > 根据另一列上的 IF 语句在 Msgbox 中显示单元格值 - Excel VBA

excel - 根据另一列上的 IF 语句在 Msgbox 中显示单元格值 - Excel VBA

问题描述

我想创建一个 Msgbox 警报，告诉我员工合同何时到期。

那些在 4 - 1 个月到期的合同将用红色填充它们的单元格：

在此处输入图像描述

到目前为止，这是我的代码：

Private Sub workbook_open()

Dim rngData As Range
Dim rngCell As Range
Dim counter As Long: counter = 0
Set rngData = Range("E4:E" & Cells(Rows.Count, Range("E3").Column).End(xlUp).Row)


For Each rngCell In rngData
    If rngCell.Value = "4" Or rngCell.Value = "3" Or rngCell.Value = "2" Or rngCell.Value = "1" And rngCell.Value <> "" Then

        counter = counter + 1
    End If
Next rngCell
MsgBox counter & " employees are reaching their contract expiration date!"

Range("A4:E13").Sort _
Key1:=Range("E4"), Order1:=xlAscending


End Sub

但是，我如何包含一行代码来告诉我合同即将到期的员工的 EE 编号，并同时显示在 Msgbox 中？

标签： excelvbaexcel-2010

解决方案

您也可以只构建一个字符串列表以在循环结束时显示。

Private Sub workbook_open()
    Dim rngData As Range
    Dim rngCell As Range
    Dim expired As String
    Dim counter As Long: counter = 0
    Set rngData = Range("E4:E" & Cells(Rows.Count, Range("E3").Column).End(xlUp).Row)
    For Each rngCell In rngData
        If rngCell.Value = "4" Or rngCell.Value = "3" Or _
           rngCell.Value = "2" Or rngCell.Value = "1" And rngCell.Value <> "" Then
            counter = counter + 1
            '--- add to the list of expired EE Numbers
            expired = expired & rngCell.Offset(0, -4).Value & ","
        End If
    Next rngCell
    expired = Left$(expired, Len(expired) - 1)   'delete the trailing comma
    MsgBox counter & " employees are reaching their contract expiration date! (" & _
           expired & ")"
    Range("A4:E13").Sort Key1:=Range("E4"), Order1:=xlAscending
End Sub

推荐阅读