VBA - formula in filtered mode.

[awsumchillicrab]

Hi,

I'm trying to apply a formula to a range of visible cells after an autofilter, to be done in coding as a macro.

After applying filters
I tried:

With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
.Formula = "=if(L2> H2+21,1,0)"
End With

but this is no good. Because in filtered mode, my first visible row is row6. This macro puts =if(L2> H2+21,1,0) in my cell instead of =if(L6> H6+21,1,0).
As row6 may not always be the first visible row in filtered mode, I don't want to hardcode a =if(L6> H6+21,1,0) into VBA.

I later tried the R1C1 kind of formula:

With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
.FormulaR1C1 = "=if(R[-5]C > R[-9]C+21,1,0"
End With

But this gives me errors.

Need expert advise

 

[VoG]

Try

With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=IF(RC[-5]>RC[-9]+21,1,0)"
End With

 

[awsumchillicrab]

oh my...

i am so careless. mixing my Rs and Cs and missing a parenthesis....

but i'm learning a lot from you VoG !

thanks!

 

[ttat01]

Try

With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=IF(RC[-5]>RC[-9]+21,1,0)"
End With

Amazing!
Thank you!)