Excel VBA变量赋值异常：RowNumberPlus1始终为初始值0

Hey, let's fix that stubborn RowNumberPlus1 variable issue you're facing. The root causes are two-fold: uninitialized variable and broken loop logic that's killing your iteration before it even gets started. Let's break this down step by step:

Key Problems in Your Original Code #

1. Uninitialized RowNumberPlus1: You set an initial value for RowNumber = 6, but RowNumberPlus1 starts at the default value 0. It only gets updated if AColValue = "" in the first loop iteration—if that condition isn't met, it stays 0 forever.
2. Misplaced Exit Do: You have an Exit Do right in the middle of your loop body, which means the loop runs exactly once and then terminates. All the code after that (like incrementing RowNumber or NumOfBkrRows) never gets executed, so your iteration logic is completely broken.

Fixed Code with Explanations #

Here's the revised code with fixes and improvements to make your logic work as intended:

Sub FormatBreakerSettingsData()
    'Define variables for the Breaker Settings Fields
    Dim DevName As String ' Changed to String (since it's for device names, not numbers)
    Dim DevName2 As String ' Same here
    Dim RowNumber As Long
    Dim RowNumberPlus1 As Long
    'Define other variables
    Dim NumOfBkrRows As Long
    Dim NumOfBkrs As Long
    Dim i As Long
    Dim j As Long
    Dim AColValue As String
    
    'Set initial values appropriately
    RowNumber = 6
    RowNumberPlus1 = RowNumber + 1 ' Initialize to avoid default 0 value
    NumOfBkrRows = 0
    NumOfBkrs = 0
    i = 0
    j = 0
    AColValue = Range("A" & RowNumber).Value ' Dynamic reference to RowNumber
    
    'Find number of breakers
    Do While j <> 1
        ' Replace messy ElseIf chain with cleaner Select Case
        Select Case AColValue
            Case "LV Fuses", "HV/MV with Trip-Unit", "HV/MV without Trip-Unit", _
                 "Relays", "MCP", "MOL", "HV Fuses", "Switches"
                j = 1 ' Trigger loop exit when we hit a target category
            Case ""
                RowNumberPlus1 = RowNumber + 1 ' Update when current row is empty
            Case Else
                NumOfBkrRows = NumOfBkrRows + 1 ' Count valid breaker rows
        End Select
        
        ' Handle consecutive empty rows cleanly
        Do While Range("A" & RowNumberPlus1).Value = ""
            RowNumberPlus1 = RowNumberPlus1 + 1
            ' Prevent infinite loop if we hit the end of the sheet
            If RowNumberPlus1 > ActiveSheet.UsedRange.Rows.Count Then
                j = 1
                Exit Do
            End If
        Loop
        
        ' Update variables for next loop iteration
        RowNumber = RowNumberPlus1
        AColValue = Range("A" & RowNumber).Value
        
        ' Exit loop if we hit an empty row (end of data)
        If AColValue = "" Then
            j = 1
        End If
    Loop
    
    ' Calculate number of breakers (add rounding if needed)
    NumOfBkrs = NumOfBkrRows / 4
End Sub

What Changed & Why #

• Initialized RowNumberPlus1: Starting with RowNumber + 1 ensures it never stays at 0, even if the first row isn't empty.
• Removed the rogue Exit Do: This was the biggest issue—your loop now runs continuously until it hits a target category or empty row.
• Switched to Select Case: Makes the category checks far cleaner and easier to maintain than a long chain of ElseIf statements.
• Fixed variable types: DevName and DevName2 were defined as Long (numbers), but they're clearly meant for device names—changed to String to avoid type errors.
• Added boundary check: Prevents infinite loops if the code reaches the end of the worksheet without finding a target category.
• Cleaner empty row handling: Uses a nested loop to skip all consecutive empty rows in one go, instead of repeating If checks.

Extra Tips #

• Specify your worksheet: Instead of relying on ActiveSheet, use ThisWorkbook.Sheets("YourSheetName") and prefix all Range calls with a dot (e.g., .Range("A" & RowNumber)) to avoid errors if the user switches sheets.
• Handle non-integer breaker counts: NumOfBkrRows / 4 might return a decimal. If you need whole numbers, use Int(NumOfBkrRows / 4) or Round(NumOfBkrRows / 4, 0) depending on your needs.

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