Excel VBA代码报错：Else without If与Next without For，求修正方案

Hey Ahmad, let's break down why you're hitting those errors and get your code working to fill cells A1:C8 with numbers 1-24.

First, Why the Errors Happen #

Your code has two key structural issues:

• Unclosed For loops inside If/ElseIf/Else blocks: Each For Count loop you start in the If CL=1, ElseIf CL=2, and Else branches doesn't have a matching Next Count inside that branch. This confuses VBA because it can't find where each Count loop ends before moving to the next conditional branch.
• Incorrect variable declaration: Dim Count, RO, CL As Integer only declares CL as an Integer—Count and RO default to Variant type. It's better to explicitly declare each variable's type to avoid unexpected behavior.

Fixed Version of Your Code #

Here's the corrected code that works as intended:

Public Sub InsertNummer()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Avoid using Activate for better reliability
    
    Dim Count As Integer, RO As Integer, CL As Integer
    
    For CL = 1 To 3
        For RO = 1 To 8
            If CL = 1 Then
                Count = RO ' A column gets 1-8, matching row number
                ws.Cells(RO, CL).Value = Count
            ElseIf CL = 2 Then
                Count = RO + 8 ' B column gets 9-16 (row +8)
                ws.Cells(RO, CL).Value = Count
            Else
                Count = RO + 16 ' C column gets 17-24 (row +16)
                ws.Cells(RO, CL).Value = Count
            End If
        Next RO
    Next CL
End Sub

Key Improvements & Fixes #

• Removed redundant For Count loops: You don't need a separate loop for Count—since each column's numbers follow a simple pattern (A=row, B=row+8, C=row+16), we can calculate Count directly based on the row and column.
• Replaced Activate with a worksheet variable: Using Activate can lead to bugs if the user switches sheets while the code runs. Referencing the worksheet directly is more reliable.
• Fixed variable declarations: All three variables (Count, RO, CL) are now explicitly declared as Integer.
• Cleaned up loop structure: The nested loops now properly close, and the If/ElseIf/Else blocks are correctly structured without unclosed loops inside them.

Even Simplified Version (Optional) #

If you want to make the code even shorter, you can calculate the cell value directly without the Count variable:

Public Sub InsertNummer_Simplified()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim RO As Integer, CL As Integer
    For CL = 1 To 3
        For RO = 1 To 8
            ws.Cells(RO, CL).Value = (CL - 1) * 8 + RO
        Next RO
    Next CL
End Sub

This formula (CL-1)*8 + RO works because:

• For column 1 (A): (1-1)*8 + RO = RO → 1-8
• For column 2 (B): (2-1)*8 + RO = 8 + RO →9-16
• For column3 (C): (3-1)*8 + RO=16+RO →17-24

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