Excel VBA运行时错误1004：Range方法调用失败求助

Hey there, let’s dig into this super frustrating Run-time Error '1004' you’re hitting with the Range method. It’s one of the most common VBA pitfalls, but we can break down the most likely causes and fixes based on your code snippet.

Common Causes & Fixes #

• Missing Worksheet Specification
  The biggest culprit here is usually not explicitly telling VBA which worksheet to target. By default, Range() uses the active sheet, which might not be the one your code is supposed to work with. Fix this by wrapping your code in a With block to lock in the correct sheet:

  Sub qq()
      Dim d(23), LastRow As Long ' We'll fix LastRow next
      Dim c(23) As String
      
      ' Replace "Sheet1" with your actual worksheet name
      With ThisWorkbook.Worksheets("Sheet1")
          ' Use .Range and .Cells to reference the sheet in the With block
          LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
          ' Example: If you're using c(i) as a range reference
          .Range(c(1)).Value = "Test"
      End With
  End Sub
  

• Incorrect Data Type for LastRow
  You declared LastRow As Integer, but Excel worksheets can have up to 1,048,576 rows—way more than the maximum value of an Integer (32,767). If your dataset exceeds that, LastRow will overflow, leading to an invalid Range reference. Swap it to Long:

  Dim LastRow As Long ' Always use Long for row counts!
  

• Invalid Array Values for Range References
  You’ve defined arrays like c(23) As String—if any of these values aren’t valid cell references (e.g., empty strings, typos like "ZZZ", or malformed addresses like "A1-"), calling Range(c(i)) will throw this error. Add a quick check to validate these values before using them:

  ' Before using c(i) in Range
  If c(i) <> "" And IsError(Application.Evaluate(c(i))) = False Then
      .Range(c(i)).Value = ...
  Else
      MsgBox "Invalid range reference: " & c(i)
  End If
  

• Protected Worksheet
  If the worksheet you’re targeting is protected (without allowing edit permissions for the cells you’re modifying), the Range method will fail. Either unprotect the sheet temporarily in your code, or adjust the protection settings to allow edits:

  ' Unprotect at the start (replace "password" if you have one)
  .Unprotect Password:="yourpassword"
  
  ' Your code here...
  
  ' Re-protect when done
  .Protect Password:="yourpassword"
  

Quick Debugging Tip #

Add breakpoints in your code (F9) and step through line by line (F8). Hover over variables like c(i) or LastRow to check their values—this will quickly reveal if a bad reference is causing the error.

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