☰

VBA 代码生成不同工作表中空白单元格的报告

tag-icon • tag-icon microsoft-excel vba macros
VBA 代码生成不同工作表中空白单元格的报告

嗨，我如何修改下面的代码，以便所有空白单元格值都生成为不同工作表中的报告，从我之前发布的不同帖子中获取此代码！但发现如果发现第一个单元格为空白，它将结束，那么如何循环所有单元格并将其作为不同 excel 中的报告

Sub Loop_Column_Row()
    
    Dim lRow, lCol As Long
    
    'Find the last non-blank cell in row 1
    'This assumes that the first row has column headers
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    'Loop through columns
    For x = 1 To lCol
        'Find the last non-blank cell in the column
        lRow = Cells(Rows.Count, x).End(xlUp).Row
    
        'Loop through rows
        'Start from row 2 as row 1 is the row with headers
        For y = 2 To lRow
            If Cells(y, x) = "" Then
                'Display message box when empty cell is found
                MsgBox "Cell in Row: " & y & " Column: " & x & " is empty"
                'Stop executing the method when 1st empty cell found
                Exit Sub
            End If
        Next y
    Next x
  
End Sub

答案1

为了防止代码在找到第一个空白单元格后停止，您需要删除Exit Sub。您需要有一个计数器，该计数器将在找到空白单元格并在报告表上的单元格上写入消息后递增（在下面的代码中，报告表是 Sheet3）。

您的代码将如下所示。i是计数器。我已删除消息框。但如果它很重要，只需恢复该行代码即可。

Sub Loop_Column_Row()
    
    Dim lRow, lCol As Long

    'Counter
    Dim i As Integer
    'Find the last non-blank cell in row 1
    'This assumes that the first row has column headers
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    i = 1
    
    'Loop through columns
    For x = 1 To lCol
        'Find the last non-blank cell in the column
        lRow = Cells(Rows.Count, x).End(xlUp).Row
    
        'Loop through rows
        'Start from row 2 as row 1 is the row with headers
        For y = 2 To lRow
            If Cells(y, x) = "" Then
                'Write in column A of Sheet3
                ThisWorkbook.Worksheets("Sheet3").Range("A" & i).Value = "Cell in Row: " & y & " Column: " & x & " is empty"
                i = i + 1
            End If
        Next y
    Next x
  
End Sub

相关内容