insert data for open positions

[av8tordude]

Column K with dates indicate the position is closed. I would like to input the following information (below) into column T for the positions that are NOT closed.
Note: The data currently indicated in column T in the attached should be the correct output.

Can anyone assists




Sub OpnPos()
Dim LRow As Long


LRow = Range("A" & Rows.Count).End(xlUp).Row


Range("T7") = WorksheetFunction.CountA(Range("K7:K15" & LRow))
Range("T8") = Format(WorksheetFunction.Sum(Range("J7:J" & LRow)), "$#,0.00")
Range("T9") = Format(WorksheetFunction.Sum(Range("P7:P" & LRow)), "$#,0.00")
End Sub26348

[av8tordude]

I solved Range("T7"),


Range("T7") = WorksheetFunction.CountIfs(Range("A7:A" & lrow), "<>" & 0, Range("K7:K" & lrow), "")

however, I need some help with T8 & T9

Please help

[paulked]

If you change the data area to a table, you can use formula to get what you want.

[av8tordude]

Ok..Thank you for the tip, but I put something together that seems to work..


Sub CountEmptyCells()Dim LRow As Long


LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("T7") = WorksheetFunction.CountIfs(Range("A7:A" & LRow), "<>" & 0, Range("K7:K" & LRow), "")
Range("T8") = Format(WorksheetFunction.SumIfs(Range("J7:J" & LRow), Range("K7:K" & LRow), ""), "$#,0.00")
Range("T9") = Format(WorksheetFunction.SumIfs(Range("P7:P" & LRow), Range("K7:K" & LRow), ""), "$#,0.00")
End Sub

[paulked]

OK, but you have to run a routine to update the values.