CODEWITHSUNDEEP
vbaexcel
3689
3689

Reputation: 113

Count if formula with VBA till last row

My below code only gives output for 2 rows, rest it is not getting applied don't know why? formula needs to applied from K20 till last row of adjacent column(J) . Can someone help me in correcting it. Thanks!

Sub SortS()
Range("K19").Select
ActiveCell.FormulaR1C1 = "Sort"

With Sheets("Sheet1")
rowlast = .Range("K" & .Rows.Count).End(xlUp).Row
With .Range("K20:K" & rowlast)
    .Formula = "=IF(COUNTIF(RC[-6]:RC[-2],""S"")>0,1,0)"
    .Value = .Value
End With
End With

End Sub

Upvotes: 0

Views: 987

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

You're not looking at column J for the last row - try changing this

rowlast = .Range("K" & .Rows.Count).End(xlUp).Row

to this

rowlast = .Range("J" & .Rows.Count).End(xlUp).Row

Upvotes: 2

Related Questions