Excel VBA开发需求：提取人员最晚考试日期及关联字段至EXAMCI表

Hey there! I get that starting out with VBA can feel overwhelming, so let's walk through a straightforward solution for your problem. We'll write code that handles the date comparison (whether D column or H column has the later date) and outputs the correct fields to your EXAMCI worksheet.

Step-by-Step Breakdown & Code #

First, here's the full VBA code you can drop into your workbook. I've added detailed comments so you can follow along and adjust it to match your exact column setup:

Sub ExtractLatestExamInfo()
    Dim srcWS As Worksheet
    Dim destWS As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim destRow As Long
    Dim latestDate As Variant
    Dim checkDate As Variant
    Dim induration As Variant
    
    ' Set references to your source worksheet (change "SourceData" to your actual sheet name)
    Set srcWS = ThisWorkbook.Worksheets("SourceData")
    ' Set reference to the destination EXAMCI sheet (create it if it doesn't exist)
    On Error Resume Next
    Set destWS = ThisWorkbook.Worksheets("EXAMCI")
    On Error GoTo 0
    If destWS Is Nothing Then
        Set destWS = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        destWS.Name = "EXAMCI"
    End If
    
    ' Clear existing data in EXAMCI (optional, but keeps it clean)
    destWS.Cells.Clear
    
    ' Write header row to EXAMCI
    destWS.Range("A1").Value = "Latest Exam Date"
    destWS.Range("B1").Value = "Exam Check Date"
    destWS.Range("C1").Value = "Exam Induration"
    destRow = 2 ' Start writing data from row 2
    
    ' Find the last row with data in your source sheet
    lastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row ' Assumes column A has unique identifiers for each person
    
    ' Loop through each row of data (start at row 2 assuming row 1 is headers)
    For i = 2 To lastRow
        ' First, handle cases where dates might be empty or invalid
        Dim dateD As Variant, dateH As Variant
        dateD = srcWS.Cells(i, "D").Value
        dateH = srcWS.Cells(i, "H").Value
        
        ' Check if both dates are valid
        If IsDate(dateD) And IsDate(dateH) Then
            ' Compare the two dates to find the latest one
            If dateD > dateH Then
                latestDate = dateD
                ' Get corresponding Check Date and Induration (adjust columns E and F if yours are different!)
                checkDate = srcWS.Cells(i, "E").Value
                induration = srcWS.Cells(i, "F").Value
            Else
                latestDate = dateH
                ' Get corresponding 2nd Exam Check Date and Induration (adjust columns I and J if yours are different!)
                checkDate = srcWS.Cells(i, "I").Value
                induration = srcWS.Cells(i, "J").Value
            End If
        ' Handle cases where only D column has a valid date
        ElseIf IsDate(dateD) Then
            latestDate = dateD
            checkDate = srcWS.Cells(i, "E").Value
            induration = srcWS.Cells(i, "F").Value
        ' Handle cases where only H column has a valid date
        ElseIf IsDate(dateH) Then
            latestDate = dateH
            checkDate = srcWS.Cells(i, "I").Value
            induration = srcWS.Cells(i, "J").Value
        ' Handle cases where neither date is valid (skip or mark as error)
        Else
            latestDate = "N/A"
            checkDate = "N/A"
            induration = "N/A"
        End If
        
        ' Write the data to EXAMCI sheet
        destWS.Cells(destRow, "A").Value = latestDate
        destWS.Cells(destRow, "B").Value = checkDate
        destWS.Cells(destRow, "C").Value = induration
        destRow = destRow + 1
    Next i
    
    ' Auto-fit columns in EXAMCI for readability
    destWS.Columns("A:C").AutoFit
    
    MsgBox "Extraction complete! Check the EXAMCI worksheet.", vbInformation
End Sub

Key Notes for Adjustments #

• Source Sheet Name: Change "SourceData" to the actual name of your input worksheet.
• Column References: I assumed:
  • Exam Check Date for D column is in column E, Exam Induration in F
  • 2nd Exam Check Date for H column is in column I, 2nd Exam Induration in J
    If your columns are different, just swap out the letters (e.g., srcWS.Cells(i, "G") instead of "E") in the code.
• Error Handling: The code skips invalid dates and marks them as "N/A"—you can tweak this part if you want to handle empty dates differently.

How to Use This Code #

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code into the module.
5. Adjust the sheet and column references as needed.
6. Press F5 to run the macro, or assign it to a button in Excel for easier access.

Content of the question comes from stack exchange, question author Nick