Run-Time error '1004'

julhs

Active Member
Joined
Dec 3, 2018
Messages
471
Office Version
  1. 2010
Platform
  1. Windows
I need some help with this runtime error:-
VBA Code:
Run-Time error '1004':
Method 'Range' of object'_Worksheet' failed
On line:-
VBA Code:
'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!!!!
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
But I’ve come back to try and resolve why I can’t get the below code to run.
Try...
VBA Code:
If Target.Address = BTgt1.Address Then
and amend the same where you use Range("BTgt1") later in the code
 
Last edited:
Upvote 0
Solution
Can you check if the Range Name BTgt1 has a scope of workbook or whether the scope is a sheet name ?
 
Upvote 0
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.
 
Upvote 0
Mark, am I right in assume I need to do the same for Range("BFLtr") as well?
 
Upvote 0
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
 
Upvote 0
@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.

Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top