I need some help with this runtime error:-
On line:-
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!!!!
VBA Code:
Run-Time error '1004':
Method 'Range' of object'_Worksheet' failed
VBA Code:
'If Target.Address = Range("BTgt1").Address Then'
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!!!!
VBA Code:
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