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

 

[julhs]

Thank you both very much.
Everything now working!!

Mark; I was in process of making changes as you suggested when Alex can back with it ready made for me!!! so thank you Alex.
Excel is allowing BFLtr as a single cell in this situation.
It’s only set for single cell because if there is a value in that ONE cell, there will be data in the range to run the filter on.
It also simply saved the need for more code to establish the dynamic filter range, probably prudent that I do so though.

Thanks again Mark & Alex

 

[julhs]

Know that is weird, having run the code twice before I posted my reply and it worked, have gone back to tidy up and it now is not actually doing the filtering??

P.S Disregard that it is OK, I “tidied” up more than I should have !!

 

[Alex Blakenburg]

Just saw your PS.
Thanks for letting us know you have it working now. Glad Mark & I could help.