vba - 在保存事件之前，需要帮助同时循环两个单元格

Question

我正在尝试执行保存前事件，如果两个给定单元格之一为空，则不允许用户保存。到目前为止，我所做的就是链接第 13 列 (M) 和单元格 A4。

我想做的是将事件应用于两个范围和行的组合:A4-A19 和 M4-M19。这样:如果A4不为空且M4为空，则会出现一个消息框并阻止保存，依此类推..A5-M5，A6-M6...直到A19-M19。如果两个相应的单元格同时为空，则应该可以保存。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim i As Integer, MyWb As Object
    i = 13
    Set MyWb = ThisWorkbook.Sheets("Planning").Cells
    Do While MyWb(4, i).Value <> ""
    i = i + 1
    Loop
    If i = 13 Then
        If ThisWorkbook.Sheets("Planning").Range("A4") <> "" Then
            MsgBox ("You will need to enter topics before saving"), vbCritical
            Cancel = True
        End If
    End If
End Sub

根据 Wolfie 的代码，我成功地获得了我想要的内容，只需为 A 列添加一个 If not isempty 并替换 19 而不是 13。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim plansht As Worksheet
Set plansht = ThisWorkbook.Sheets("Planning")

' Loop over rows
Dim rw As Integer
For rw = 4 To 19
    ' Test if both the A and M column in row "rw" are blank
    If Not IsEmpty(plansht.Range("A" & rw)) And plansht.Range("M" & rw).Value = "" Then
        MsgBox ("You will need to enter topics before saving"), vbCritical
        Cancel = True
    End If
Next rw

End Sub

Answer 1

试试这个:

For i = 4 to 19
    If ThisWorkbook.Sheets("Planning").Range("A" & i) <> "" AND _
       ThisWorkbook.Sheets("Planning").Range("M" & i) <> ""  Then
          MsgBox("Hey bro you didn't wrote properly on line " & i)
          Cancel = True

Next i