AutoFilter visible cell only works when stepping through

[UWMmakow]

Hey everyone, I have a sheet that houses data for many stores, and it also has extra "blank" stores (e.g. Empty Store 1000, Empty Store 1001...) built into it which can be changed to accommodate new stores in the future. So that's what I'm working on now, is code to change 1000 Empty Store to 123 RandomCity (or whatever the user inputs, of course).

I use AutoFilter to find all the rows (513 rows) that contain 1000 Empty Store, and then select the visible cells and change the values accordingly. It looks like this:

Sub newStore()
    Dim storeLocation As String
    Dim storeNumber As Integer
    Dim response As Integer
    Dim emptyStoreNum As Integer
    Dim r As Range
    Dim lastRow As Long
    
    lastRow = 51306
    
    Application.EnableEvents = False
    Application.ScreenUpdating = True

'...
'a bunch of code asking user what the new store location and number are
'...

'confirm that the user wants to add a store
    response = MsgBox("You have selected to add a new store with the following name: " & storeNumber & " " & storeLocation & vbNewLine & vbNewLine & "Are you sure you want to add this store? This cannot be undone.", 3, "Confirm New Store")
    
    Select Case response
        'user selects Yes
        Case 6

'on Monthly-Weekly Data sheet, get every row with "XXXX Empty Store" in col "AE" and replace Z, AA, and AE with new store info
            Sheets("Monthly-Weekly Data Sheet").Range("AE1:AE" & lastRow).AutoFilter Field:=1, Criteria1:=emptyStoreNum & " Empty Store"
            
            Set r = Range("AE7:AE" & lastRow)
            r.SpecialCells(xlCellTypeVisible).Value = storeNumber & " " & storeLocation 'ERROR HERE
            
            Set r = Range("Z7:Z" & lastRow)
            r.SpecialCells(xlCellTypeVisible).Value = storeNumber
            
            Set r = Range("AA7:AA" & lastRow)
            r.SpecialCells(xlCellTypeVisible).Value = storeLocation

'last bit of the Sub

So if I step through the code everything works perfectly fine. If I just run it outright (either by pressing the "Add Store" button on one of the sheets the way the user would, or if I hit F5 in the VBA editor), it errors out at the indicated line. But after I end the error'd sub, the autofilter is still on and it filtered correctly.

The error is 1004, "No cells were found."

The range I set to search through for visible cells is AE7:AE51306, but AE69 is the first row displayed after the filter. That shouldn't have any effect though, right?

Any thoughts?

 

[JLGWhiz]

Try changing this:

Set r = Range("AE7:AE" & lastRow)

To this:

Set r = Sheets("Monthly-Weekly Data Sheet").Range("AE7:AE" & lastRow)

And the other two set statements would need the same modification.
Otherwise r refers to whichever sheet happens to be active when the Set r line executes.

You could do it this way

With Sheets("Monthly-Weekly Data Sheet")
    Set r = .Range("AE7:AE" & lastRow)
    r.SpecialCells(xlCellTypeVisible).Value = storeNumber & " " & storeLocation 'ERROR HERE

    Set r = .Range("Z7:Z" & lastRow)
    r.SpecialCells(xlCellTypeVisible).Value = storeNumber

    Set r = .Range("AA7:AA" & lastRow)
    r.SpecialCells(xlCellTypeVisible).Value = storeLocation
End With

 

[UWMmakow]

Son of a gun, that's right. Rookie mistake xD

Thanks for catching that. I've been scratching my head for the past hour.

 

[JLGWhiz]

Son of a gun, that's right. Rookie mistake xD

Thanks for catching that. I've been scratching my head for the past hour.

Anybody who says they have never done it is lying!
Regards, JLG