VBA数组声明与If函数问题求助：Mtable编译错误

Hey there! Let's work through the issues in your VBA code to fix that compile error with Mtable and other potential bugs that might cause problems later.

问题分析 #

Your code has a few key issues that are causing the compile error and could lead to unexpected behavior:

• Mtable is not declared: VBA requires explicit variable declaration (unless you have Option Explicit turned off, which you shouldn't!). Not declaring variables can lead to typos and compile errors.
• Incorrect data types for lrow and lcol: You declared them as Range objects, but you're trying to store numeric row/column numbers. You should use Long (since Excel has more rows than the Integer limit of 32767).
• Unqualified Range references: Most of your Range and Cells calls don't specify which worksheet they belong to. This defaults to the active worksheet, which might not be Branches and will cause errors.
• Unused array Mtable: You assign a range to Mtable but never actually use it in your loop—this is unnecessary and can be removed unless you plan to use the array later.

修正后的代码 #

Option Explicit ' Always add this at the top to enforce variable declaration

Sub GatheringofExpense()
    Dim Branches As Worksheet
    Dim Final As Worksheet
    Dim i As Long ' Use Long instead of Integer for row counters
    Dim lrow As Long
    Dim lcol As Long
    
    Set Branches = Worksheets("Branches")
    Set Final = Worksheets("Final")
    
    ' Get last row and column from the Branches worksheet
    lrow = Branches.Range("A1000000").End(xlUp).Row
    lcol = Branches.Range("XFD4").End(xlToLeft).Column
    
    ' Loop through rows starting from row 4 (since your range starts at row 4)
    For i = 4 To lrow ' Start at 4 because your original range was Cells(4,1)
        ' Fully qualify all Range references with the Branches worksheet
        If Branches.Range("A" & i) = "Barda" And Branches.Range("B" & i) = "Fuzuli" Then
            ' Copy the entire row from column A to last column
            Branches.Range(Branches.Cells(i, 1), Branches.Cells(i, lcol)).Copy
            ' Paste to the next empty row in Final
            Final.Range("A1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
        End If
    Next i
    
    ' Clear the clipboard to remove the "marching ants"
    Application.CutCopyMode = False
End Sub

关键修改说明 #

1. Added Option Explicit: This forces you to declare all variables, catching typos and undeclared variables like Mtable before they cause compile errors.
2. Fixed data types: Changed lrow/lcol to Long and i to Long to handle large row numbers in Excel.
3. Qualified all Range references: Every Range and Cells call now specifies Branches or Final to avoid relying on the active worksheet.
4. Removed unused Mtable array: Since you weren't using it in the loop, we removed it to clean up the code. If you intended to use the array for faster processing, let me know and we can adjust that!
5. Adjusted loop start: Changed i = 1 to i = 4 because your original range started at row 4—this avoids checking rows 1-3 which aren't part of your data table.
6. Added clipboard cleanup: Application.CutCopyMode = False clears the copy selection after pasting, which is good practice.

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