Excel VBA循环输出问题：修改代码实现指定格式及补全行

Alright, let's break down the problem and work through solutions for both formatting and missing data in your VBA code for the OptieRestricties worksheet.

Original VBA Code #

Here's the initial code you're working with:

Private Sub CommandButton_Click()
 Dim i As Long
 Dim p As Long
 Dim Item As String
 Dim ifcond As String
 Dim thencond As String
 Excel.Worksheets("OptieRestricties").Select
 With ActiveSheet
 i = 2
 Do Until IsEmpty(.Cells(i, 2))
 p = 4
 Do Until IsEmpty(.Cells(2, p))
 ifcond = ActiveSheet.Cells(i, 2)
 thencond = ActiveSheet.Cells(i, 3)
 Item = ActiveSheet.Cells(i, p)
 If Not IsEmpty(Item) Then
 Debug.Print Item & " --- " & ifcond & " " & thencond
 End If
 p = p + 1
 Loop
 i = i + 1
 Loop
 End With
End Sub

Initial Output #

The code currently outputs a single line like this:

Kraker_child_1 --- 775.value=0 775.visible=0;775.udf2=0;

First Requirement: Adjust Output Format #

You need to modify the code to support additional columns (F, G, H, etc. after column E) and produce output in this format:

Kraker_child_1 --- 775.value=1 775.visible=1
Kraker_child_1 --- 775.value=0 775.visible=0;775.udf2=0;
Kraker_child_1 --- 775.value=0 775.visible=0;775.udf2=0;

Updated Issue: Format Still Doesn't Match Expectations #

After applying Paul's code, the output looks like this:

child_3 ---> 775.value=1 >>> 775.visible=1
child_2 ---> 775.value=0 >>> 775.visible=0;775.udf2=0;

But your desired output is:

child ---> 775.value=1 >>> 775.visible=1
child ---> 775.value=1 >>> 775.visible=1
child_3 ---> 775.value=1 >>> 775.visible=1
child_2 ---> 775.value=0 >>> 775.visible=0;775.udf2=0;

Updated 2: Missing Rows in Output #

When processing 111 rows of data with Paul's latest code, you should get 223 lines of output, but only 174 are appearing (49 rows missing). We need to fix this gap.

Solution Code #

Here's a revised version of the code that addresses both formatting and missing data issues:

Private Sub CommandButton_Click()
    Dim i As Long
    Dim p As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim Item As String
    Dim ifcond As String
    Dim thencond As String
    
    ' Work directly with the worksheet without selecting it (more reliable)
    With Excel.Worksheets("OptieRestricties")
        ' Get the last row with data in column B (column 2)
        lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        ' Get the last column with data in row 2
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        
        ' Loop through each row starting from row 2
        For i = 2 To lastRow
            ' Skip rows where column B is empty
            If IsEmpty(.Cells(i, 2)) Then GoTo NextRow
            
            ' Grab the condition values from columns B and C
            ifcond = .Cells(i, 2).Value
            thencond = .Cells(i, 3).Value
            
            ' Loop through all columns starting from column D (column 4)
            For p = 4 To lastCol
                Item = .Cells(i, p).Value
                ' Print every non-empty Item with the required format
                If Not IsEmpty(Item) Then
                    Debug.Print Item & " ---> " & ifcond & " >>> " & thencond
                End If
            Next p
            
NextRow:
        Next i
    End With
End Sub

Key Fixes & Improvements #

1. Eliminate missing rows: Instead of using Do Until IsEmpty which stops at the first empty cell, we calculate lastRow and lastCol to get the full range of data. This ensures we don't skip any rows/columns with data even if there are empty cells in the header row or data rows.
2. Consistent formatting: The code uses the ---> and >>> separators as seen in your desired output, and prints every non-empty Item value in columns 4+ on its own line.
3. More reliable code: We removed the Select operation and use the With block directly to reference the worksheet, which avoids issues if another sheet is active when the button is clicked.
4. Skip empty rows: We add a check to skip rows where column B is empty, preventing unnecessary processing.

This should produce the exact output format you need and ensure no rows are missing from the output.

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