excel - 在 Excel 中使用命名范围进行搜索时，运行时错误 1004 object_global 的方法范围失败

Question

我收到错误:

Run time error 1004 method range of object_global failed

在以下代码行中:

设置 br = Range("rg").Find(Range("$d$3").Value)

完整代码如下:

Private Sub ComboBox1_Change()
Dim y As String
Dim q As Variant
Dim j As Double
Dim i As Variant
Dim k As Variant
Dim l As Double
Dim qst As Variant
Dim br As Range
Dim bra As Variant

qst = MsgBox("Entire data will be removed & archived" & vbNewLine & "Are you sure you want to proceed?", vbYesNo, fnb)
If qst = vbYes Then
Worksheets("Marcopolo").Unprotect myp
Worksheets("Marcopolo").Activate

Dim arch_iv(1 To 5, 1 To 2) As Variant

Dim rg As Variant
Dim fd As Variant

For rg = 1 To 5
    For fd = 1 To 2
        arch_iv(rg, fd) = Worksheets("arrays").Cells(rg, fd).Value
    Next fd
Next rg


For rg = 1 To 5
    For fd = 1 To 2
        Set br = Range("rg").Find(Range("$d$3").Value)
            br.Select
                br.Offset(0, 2).Select
                  bra = ActiveCell.Address
                MsgBox bra
            Range("fd").Copy
        Worksheets("marcopolo").Range(bra).PasteSpecial Paste:=xlPasteValues
    Next fd
Next rg

Else
GoTo Gtout
End If

Answer 1

出现错误，因为找不到指定的范围。如果命名范围 rg 应位于“Marcopolo”工作表上，则应按如下方式引用:

With Worksheets("Marcopolo")
    Set br = .Range("rg").Find(Range("D3"))
    If br Is Nothing Then
        MsgBox "D3 NOT FOUND!"
        Exit Sub
    End If
end with

上面还有另一个问题，因为 Range("D3") 的父级没有明确定义。因此，它是代码所在的 ActiveSheet 或工作表。为了避免此问题，请考虑像这样定义它 Worksheets("SomeName").Range("D3")。