时间戳

首页 > 解决方案 > 查找所有最早的日期并在消息框中显示它们

vba - 查找所有最早的日期并在消息框中显示它们

问题描述

我有这段代码可以在我的工作表中找到日期最早的学生，并显示有关该学生的一些信息。但问题是这个子只显示了其中一个学生。我有不止一个学生有相同的最早日期，我想把他们都显示在一个消息框中。谁能帮我解决这个问题。提前致谢！:)

Sub FindMin()
Dim Mn As Long
Mn = Application.Match(Application.Min(Range("D1:D18289")), Range("D1:D18289"), 0)

MsgBox ("For the oldest students: " & Range("D" & Mn) & " the following applies: 
PROGRAM_TYPE_NAME: " & Range("k" & Mn) & ", STUDENT_ID: " & Range("L" & Mn) & " and Convertet ENROLL_PERIOD: " & Range("M" & Mn))

End Sub

标签： vbaexcel

解决方案

我会找到最小值，然后遍历所有行。

这也可以通过使用数组循环来加快速度，但取决于可能不需要的数据集的大小。

Sub FindMin()
    Dim minValue As Date
    Dim i As Long
    Dim lastRow As Long

    minValue = Application.Min(Cells(1, 4).EntireColumn)
    lastRow = Cells(Rows.Count, 4).End(xlUp).Row

    For i = 1 To lastRow
        If Cells(i, 4) = minValue Then
            MsgBox ("For the oldest students: " & Cells(i, 4) & " the following applies: PROGRAM_TYPE_NAME: " & Cells(i, 11) & ", STUDENT_ID: " & Cells(i, 12) & " and Convertet ENROLL_PERIOD: " & Cells(i, 13))
        End If
    Next I
End Sub

我还建议包括一种离开循环的方法，以防您不想查看所有内容，因为 MessageBox 会很烦人。这样做很简单

Sub FindMin()
    Dim minValue As Date
    Dim i As Long
    Dim lastRow As Long
    Dim text As String

    minValue = Application.Min(Cells(1, 4).EntireColumn)
    lastRow = Cells(Rows.Count, 4).End(xlUp).Row

    For i = 1 To lastRow
        If Cells(i, 4) = minValue Then
            text = "For the oldest students: " & Cells(i, 4) & " the following applies: PROGRAM_TYPE_NAME: " & Cells(i, 11) & ", STUDENT_ID: " & Cells(i, 12) & " and Convertet ENROLL_PERIOD: " & Cells(i, 13)
            If MsgBox(text, vbOKCancel) = vbCancel Then
                Exit Sub
            End If
        End If
    Next i
End Sub

推荐阅读