VBA技术求助：_Worksheet对象的Range方法调用失败，无法复制单元格值到数组

Hey there! Let's break down why you're hitting this error and how to fix it quickly.

The Root Cause #

The error pops up on this line:

raw_data = Sheet2.Range(Cells(3, 5), Cells(lr, lc))

When you use Cells(3,5) without specifying a worksheet, VBA defaults to using the currently active worksheet. If Sheet2 isn't the active sheet when this code runs, you're trying to create a Range on Sheet2 using cells from a different worksheet—and that's a conflict VBA can't resolve.

Simple Fixes #

You have two straightforward ways to fix this:

1. Explicitly specify the worksheet for every Cells reference
   Update the problematic line to include Sheet2. before each Cells:

   raw_data = Sheet2.Range(Sheet2.Cells(3, 5), Sheet2.Cells(lr, lc))
   

2. Use a With block to simplify your code
   This makes all references inside the block automatically point to Sheet2, so you don't have to repeat Sheet2. every time:

   Dim raw_data() As Variant, rc_fdata() As Variant, src_fdata() As Variant, fc_fdata() As Variant, efc_fdata() As Variant
   Dim last_entry As Long, lr As Long, lc As Long
   Dim rc_s As Single, src_s As Single, fc_s As Single, efc_s As Single
   Dim rr As Range, rc_p As Range
   Dim w As Integer, x As Integer, y As Integer, z As Integer, c As Integer, r As Integer
   
   w = 1 'initialize array column counters
   x = 1
   y = 1
   z = 1
   
   With Sheet2
       lr = .Range("D4").SpecialCells(xlCellTypeLastCell).Row 'holds last row
       lc = .Range("D4").SpecialCells(xlCellTypeLastCell).Column 'holds last column
       ReDim raw_data(1 To lr, 1 To lc)
       raw_data = .Range(.Cells(3, 5), .Cells(lr, lc)) 'placing raw data in array
   End With
   

   Notice the dots before Range and Cells—that's how they link to the Sheet2 specified in the With statement.

Bonus: More Reliable Last Row/Column Detection #

SpecialCells(xlCellTypeLastCell) can sometimes give incorrect results if you've deleted data from the sheet before (it retains the last cell that ever had data). For more accuracy, use these methods instead:

• Get last row in column D: lr = .Cells(.Rows.Count, "D").End(xlUp).Row
• Get last column in row 4: lc = .Cells(4, .Columns.Count).End(xlToLeft).Column

Replace the existing lr and lc lines with these to ensure you're always grabbing the actual last used row/column.

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