求助：编写VBA宏检测过期日期并弹出相邻单元格内容的MsgBox

Hey there! Let's get this macro sorted out for you—since you're new to VBA, I'll break it down step by step so you understand exactly what's going on.

First, here's the full working code that meets all your requirements:

Sub CheckExpiredDates()
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim cell As Range
    
    ' Update these two lines to match your worksheet and target area
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace with your sheet name
    Set targetRange = ws.Range("A1:A100") ' Replace with your actual range
    
    ' Loop through every cell in the target range
    For Each cell In targetRange
        ' First, make sure the cell contains a valid date (critical for text cells!)
        If IsDate(cell.Value) Then
            ' Check if the date is earlier than today
            If cell.Value < Date Then
                ' Show a message with the adjacent cell's content (right side here)
                MsgBox "已过期项目：" & cell.Offset(0, 1).Value, vbInformation, "过期提醒"
            End If
        End If
    Next cell
    
    MsgBox "检查完成！", vbInformation, "结束提示"
End Sub

Key Details to Note: #

• Worksheet & Range Setup: The lines Set ws = ... and Set targetRange = ... need your input—replace "Sheet1" with your actual worksheet name, and "A1:A100" with the range where your dates are stored.
• IsDate Check: We absolutely need this to skip over text cells, which would cause errors if we tried to compare them to a date.
• Date Comparison: Date is a built-in VBA function that grabs today's date from your system. We just check if the cell's date is earlier than that.
• Adjacent Cell: cell.Offset(0, 1) gets the cell directly to the right of the date cell. If you need the left cell instead, use Offset(0, -1); for the cell above, use Offset(-1, 0).

Quick Setup Steps for You: #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer → Insert → Module.
3. Paste the code into the new module window.
4. Adjust the worksheet name and target range to match your data.
5. Run the macro by pressing F5 in the editor, or go back to Excel and run it via the Developer tab → Macros.

Bonus Tip for Dynamic Data: #

If your date list grows over time (you add new rows regularly), replace the targetRange line with this to automatically include all filled rows in your date column:

Set targetRange = ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp))

Just change "A" to the column letter where your dates are stored.

内容的提问来源于stack exchange，提问作者J Dodge