VBA循环出现Application-defined or object-defined error报错求助

Hey there, let's figure out why your code is crashing when j=2—that error almost always means your code is trying to access a cell that doesn't exist, or using a variable that hasn't been set up properly. Let's break down the issues and fix them:

Key Problems in Your Original Code #

1. Uninitialized variable i
   You’re using Offset(-i, -3) and Offset(-i, -2) but never defined or assigned a value to i! In VBA, uninitialized variables default to 0, which might not be what you want—but even worse, this is likely a typo: you meant to use j instead of i here, right?

2. Invalid cell access when j=2
   Your ProcCell is set to D2. When j=2, ProcCell.Offset(-j, 0) tries to access D0—Excel doesn’t have a row 0! That’s exactly why the code crashes at j=2.

3. Redundant nested loops
   You’ve got a Do While check = False loop wrapping your For j loop, which is unnecessary. Once check becomes True, you exit the For loop but the Do loop will keep running (though it won’t do anything, since check is now True). This just complicates the code.

4. Unreliable row count calculation
   Using .Range("C2", .Range("C2").End(xlDown)).Rows.Count can give you a huge number if there are no values below C2 (since End(xlDown) will jump to the last row of the sheet), making it even more likely you’ll try to access invalid rows.

Fixed Code with Explanations #

Here’s a cleaned-up version that fixes all these issues, plus adds clarity and safety:

Option Explicit ' Always use this to catch uninitialized variables!

Sub RetrieveMaterialInfo()
    Dim wsAdmin As Worksheet
    Dim wsMaterial As Worksheet
    Dim targetMaterial As Variant
    Dim procStaticID As Variant
    Dim firstInstructionID As Variant
    Dim isFound As Boolean
    Dim currentRow As Long
    Dim startSearchRow As Long
    
    ' Initialize variables and set sheet references
    isFound = False
    Set wsAdmin = ThisWorkbook.Sheets("Admin")
    Set wsMaterial = ThisWorkbook.Sheets("material")
    
    ' Replace this with the actual cell where your material value is stored in the "material" sheet
    targetMaterial = wsMaterial.Range("A1").Value
    
    ' Start searching from the row above D2 (D1) and go UP to row 1
    startSearchRow = wsAdmin.Range("D2").Row - 1
    
    ' Loop upward from D1 to the top of the sheet
    For currentRow = startSearchRow To 1 Step -1
        If wsAdmin.Cells(currentRow, "D").Value = targetMaterial Then
            ' Grab values from columns A and B of the matching row
            procStaticID = wsAdmin.Cells(currentRow, "A").Value
            firstInstructionID = wsAdmin.Cells(currentRow, "B").Value
            isFound = True
            Exit For ' Stop searching at the first match
        End If
    Next currentRow
    
    ' Optional: Alert if no match was found
    If Not isFound Then
        MsgBox "Target material not found in column D (above row 2) of the Admin sheet."
    End If
End Sub

What Changed? #

• Removed invalid row access: We start searching from D1 (row 1) instead of trying to go above row 1, so no more D0 errors.
• Fixed variable mix-up: Replaced the mysterious i with explicit row references using Cells(currentRow, "A")—this is easier to read and avoids Offset confusion.
• Simplified logic: Got rid of the redundant Do While loop; a single For loop handles the upward search perfectly.
• Added Option Explicit: This forces you to declare all variables, catching typos like the original i mistake before your code runs.
• Clearer variable names: Renamed check to isFound and Material to targetMaterial to make the code more readable.

Quick Tip #

Enable Option Explicit permanently in the VBA Editor: Go to Tools > Options > Editor and check "Require Variable Declaration". This will save you from countless bugs caused by uninitialized or misspelled variables.

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