Run-Time error '1004'

[julhs]

I need some help with this runtime error:-

Run-Time error '1004':
Method 'Range' of object'_Worksheet' failed

On line:-

'If Target.Address = Range("BTgt1").Address Then'

I do have a lot more compact WORKING version of the code below, using "NamedRanges" for BFLtr & BTgt1
But I’ve come back to try and resolve why I can’t get the below code to run.
I’m clearly missing some fundament things but I can’t figure them out!!!!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim sht As Worksheet
Dim rng As Range
Dim FrwD As Long
Dim LrwD As Long
Dim BFLtr As Range
Dim BTgt1 As Range
Dim BTgt2 As Range

Set sht = ThisWorkbook.ActiveSheet
Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
FrwD = rng.Row
LrwD = sht.Cells(sht.Rows.Count, "AT").End(xlUp).Row
Set BFLtr = sht.Range("AQ" & FrwD + 2)
Set BTgt1 = sht.Range("AU" & FrwD - 2)
Set BTgt2 = sht.Range("AV" & FrwD - 2)
'-------------------------------
Debug.Print "Target: " & Target.Address                '= Target: $AU$22 (which is "BTgt1" address)
'Debug.Print BFLtr
'Debug.Print "BFLtr: " & Range("BFLtr").Address
'Debug.Print Range("BTgt1")
'Debug.Print Range("BTgt1").Address
'-------------------------------
If Target.Address = Range("BTgt1").Address Then       '1004' ERROR ON THIS LINE
If ("BTgt1") = "All Details" Then
Range("BFLtr").AutoFilter
Else
Range("BFLtr").AutoFilter field:=3, Criteria1:=Range("BTgt1")
'-------------------------------
'Debug.Print Range("BFLtr")
'Debug.Print Range("BFLtr").Address
'Debug.Print "BFLtr: " & BFLtr.Address
'Debug.Print Range("BTgt1")
'Debug.Print Range("BTgt1").Address
'Debug.Print "BTgt1: " & BTgt1.Address
'Debug.Print Range("BTgt2")
'Debug.Print Range("BTgt2").Address
'Debug.Print "BTgt2: " & BTgt2.Address
'-------------------------------
End If
End If
End Sub

 

[MARK858]

But I’ve come back to try and resolve why I can’t get the below code to run.

Try...

If Target.Address = BTgt1.Address Then

and amend the same where you use Range("BTgt1") later in the code

 

[Alex Blakenburg]

Can you check if the Range Name BTgt1 has a scope of workbook or whether the scope is a sheet name ?

 

[MARK858]

I do have a lot more compact WORKING version of the code below, using "NamedRanges" for BFLtr & BTgt1
But I’ve come back to try and resolve why I can’t get the below code to run.

@Alex Blakenburg I think you have misinterpreted the question. The OP has the named range code working but is querying why their previous code (posted in the question) isn't working.

 

[julhs]

Mark, am I right in assume I need to do the same for Range("BFLtr") as well?

 

[MARK858]

Yes, you have already defined BFLtr and BTgt1 as ranges earlier in the code
You might need to come back when you get to the Autofilter

 

[Alex Blakenburg]

@Alex Blakenburg I think you have misinterpreted the question. The OP has the named range code working but is querying why their previous code (posted in the question) isn't working.

Oops, you are correct I zoomed in on the Named Range bit without going through the code.

Here are how the changes look to me.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim FrwD As Long
Dim LrwD As Long
Dim BFLtr As Range
Dim BTgt1 As Range
Dim BTgt2 As Range

Set sht = ThisWorkbook.ActiveSheet
Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
FrwD = rng.Row
LrwD = sht.Cells(sht.Rows.Count, "AT").End(xlUp).Row
Set BFLtr = sht.Range("AQ" & FrwD + 2)
Set BTgt1 = sht.Range("AU" & FrwD - 2)
Set BTgt2 = sht.Range("AV" & FrwD - 2)

If target.Address = BTgt1.Address Then
    If BTgt1.Value = "All Details" Then
        BFLtr.AutoFilter
    Else
        BFLtr.AutoFilter field:=3, Criteria1:=BTgt1
    End If
End If

End Sub

 

[MARK858]

I'm a bit concerned that BFLtr is a single cell but in the autofilter it wants field 3. Having said that I have seen Excel allow it before.

 

[Alex Blakenburg]

I'm a bit concerned that BFLtr is a single cell but wants field 3. Having said that I have seen Excel allow it before.

I had that concern as well and was going to throw in current region but when I tested it, it effectively defaults to current region.
It does mean that cell needs to be connected to the surrounding data, if it has empty cells around it then current region may well return just that cell and it will error out.

 

[MARK858]

was going to throw in current region

I was going to resize it if it became an issue