Filter, Special Cells and Formula

[Kinez101]

I am trying to create a macro to change value of the visible cells in column Y from zCSS to zContractAdmin based on the value of column K. This is the macro I have tried to use but it's giving me error on Formula. Any help is greatly appreciated.

Sub Macro2()


    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveWindow.LargeScroll ToRight:=1
    ActiveSheet.Range("$A$1:$BR$32000").AutoFilter Field:=25, Criteria1:="zCSS"
    Columns("Y:Y").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.SpecialCells(xlCellTypeFormula).Formula="=IF((OR(K:K="C075",K:K="C099",K:K="C170",K:K="C228",K:K="D000",K:K="D060",K:K="D113")),"zContract Admin","zCSS")"
    
End Sub

 

[MARK858]

Untested but what do you get with

Sub Macro2()


    Application.CutCopyMode = False
 
    ActiveSheet.Range("$A$1:$BR$32000").AutoFilter Field:=25, Criteria1:="zCSS"
    Columns("Y:Y").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeFormula).Formula = "=IF((OR(K:K=""C075"",K:K=""C099"",K:K=""C170"",K:K=""C228"",K:K=""D000"",K:K=""D060"",K:K=""D113"")),""zContract Admin"",""zCSS"")"
    
End Sub

 

[Kinez101]

It gives Run-time error 1004 - Unable to get SpecialCells property of the Range class...

 

[Fluff]

Has this been sorted here?
https://www.mrexcel.com/forum/excel...a-based-filter-visible-cells.html#post4912903

 

[Kinez101]

Yes, it has. It's just running slow because it's looking at each cell.

 

[MARK858]

Does the below error?

Sub Macro2()


    Application.CutCopyMode = False
 
    ActiveSheet.Range("$A$1:$BR$32000").AutoFilter Field:=25, Criteria1:="zCSS"

    Columns("Y:Y").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeFormulas).Formula = "=IF((OR(K:K=""C075"",K:K=""C099"",K:K=""C170"",K:K=""C228"",K:K=""D000"",K:K=""D060"",K:K=""D113"")),""zContract Admin"",""zCSS"")"

End Sub