Run time error 1004 method range of object_global failed on a search in Excel with a named range

user_10544010 0
Viewed 42

I am getting error:

Run time error 1004 method range of object_global failed

in the following code line:

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

Following is the complete code:

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
vba excel
1 Answers

The error comes, because the named range is not found. If the named range rg should be on the "Marcopolo" worksheet, then it should be referred like this:

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

The above has another problem, because the parent of the Range("D3") is not explicitly defined. Thus, it is either the ActiveSheet or the Worksheet, in which the code is residing. To avoid this problem, consider defining it like this Worksheets("SomeName").Range("D3").

user_54486260
Related