Excel VBA技术问询：列B值变化时插入行并填充指定内容遇阻

Hey there! Let's get that "Department Total:" text showing up in the first blank row like you need. It's great you already have the third-row concatenation working—we just need to tweak your row insertion logic to handle the first row text correctly.

First, let's look at a revised version of your VBA subroutine that covers all your requirements: inserting 3 blank rows when B column values change, writing "Department Total:" in the first inserted row, and building that concatenated string in the third inserted row.

Sub InsertDeptRowsAndAddText()
    Dim lastRow As Long
    Dim i As Long
    
    ' Speed things up by turning off screen updates
    Application.ScreenUpdating = False
    
    ' Find the last row with data in column B
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' Loop from bottom to top to avoid missing rows after insertion
    For i = lastRow To 2 Step -1
        ' Check if current B cell is different from the one above it
        If Cells(i, "B").Value <> Cells(i - 1, "B").Value Then
            ' Insert 3 blank rows right below the current row
            Rows(i & ":" & i + 2).Insert Shift:=xlDown
            
            ' Write "Department Total:" in the FIRST inserted row (this is now row i)
            Cells(i, "A").Value = "Department Total:" ' Change column letter if needed
            
            ' Build and write the department string in the THIRD inserted row (row i+2)
            ' We use Cells(i-1, "B") because that's the last row of the previous department group
            Cells(i + 2, "A").Value = "Department " & Cells(i - 1, "B").Value & "#" ' Adjust column as needed
        End If
    Next i
    
    ' Turn screen updates back on
    Application.ScreenUpdating = True
    MsgBox "All done! Rows added and text inserted successfully.", vbInformation
End Sub

Key fixes and details: #

• Bottom-up looping: Looping from the last row to the second prevents missing value changes. If you loop top-to-bottom, inserting rows shifts unprocessed rows down, leading to skipped checks.
• Precise row targeting: After inserting 3 rows at row i, the first blank row is row i (since we shifted down). This lets us directly write "Department Total:" to that row without messy offset calculations.
• Accurate department value reference: Cells(i-1, "B") grabs the last value of the preceding department group—exactly what we need for the third row's concatenated string.
• Optional column adjustment: Swap the "A" in Cells(i, "A") or Cells(i+2, "A") to the column letter where you want the text to appear.

Give this a test run with your Excel file, and let me know if you need to adjust anything to fit your exact setup!

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