CODEWITHSUNDEEP
excelvbaruntime-errornamed-ranges
user10427053
user10427053

Reputation:

What range should i do?

Just wondering, why everytime I debug it will appear

Runtime Error 1004; Method 'Range' of object '_Global' failed

and it will be highlight at row 

 DataSH.Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("Data!$L$8:$L$9"), CopyToRange:=Range("Data!$N$8:$T$8"), _
            Unique:=False

If the coding is in forms what should the range be? And the range is referred to Master Data sheet. Can anyone help me? 

Private Sub cmdContact_Click()
        Dim DataSH As Worksheet
        Set DataSH = Sheet1
        DataSH.Range("L8") = Me.cboHeader.Value
        DataSH.Range("L9") = Me.txtSearch.Text
        DataSH.Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Data!$L$8:$L$9"), CopyToRange:=Range("Data!$N$8:$T$8"), _
        Unique:=False
        lstEmployee.RowSource = Sheet1.Range("outdata").Address(external:=True)
    End Sub

Upvotes: 1

Views: 72

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

Range("Data!$L$8:$L$9")

This unqualified Range call is in fact implicitly qualified with ActiveSheet, via a little bit of default member trickery and a bit of indirection. To keep things simple, we can say that this would be equivalent:

ActiveSheet.Range("Data!__:__")

There's the problem: the address string is qualified with a sheet name, and the Range we mean to get with that string is taken from whatever the ActiveSheet is. If there's a match, we're lucky. Otherwise, boom.

So here we are. Does this work?

ActiveSheet.Range("Data!$L$8:$L$9")

Because this exemplified code throws the same error you're getting, for the same reason:

Sheet1.Range("Sheet2!A1")

Upvotes: 1

Related Questions