Hello
As part of a macro I want to put a countif formula in. The code below works but, the problem is the starting cell this can change each tiem I run report.
For example cell J66 may be J80 and cell B66 may be B80 next time. The find will put me to that starting row, I want to know how to get that into my formula. I've tried ranges without any success.
Any ideas would be much appreciated.
Macro Code
Range("A3").Select
Cells.Find(What:="EHP", After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
ListEnd2 = ActiveCell.Offset(0, 0).Row
Range("J3").Select
Selection.End(xlDown).Select
Selection.Offset(6, 0).Select
ActiveCell.Formula = _
"=COUNTIF(J66:J" & ListEnd2 & ",""Qualifies"")/COUNTA(B66:B" & ListEnd2 & ")"
Selection.NumberFormat = "0%"
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=COUNTIF(J66:J" & ListEnd2 & ",""Qualifies"")"
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=COUNTIF(J66:J" & ListEnd2 & ",""No"")"
coop123
As part of a macro I want to put a countif formula in. The code below works but, the problem is the starting cell this can change each tiem I run report.
For example cell J66 may be J80 and cell B66 may be B80 next time. The find will put me to that starting row, I want to know how to get that into my formula. I've tried ranges without any success.
Any ideas would be much appreciated.
Macro Code
Range("A3").Select
Cells.Find(What:="EHP", After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
ListEnd2 = ActiveCell.Offset(0, 0).Row
Range("J3").Select
Selection.End(xlDown).Select
Selection.Offset(6, 0).Select
ActiveCell.Formula = _
"=COUNTIF(J66:J" & ListEnd2 & ",""Qualifies"")/COUNTA(B66:B" & ListEnd2 & ")"
Selection.NumberFormat = "0%"
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=COUNTIF(J66:J" & ListEnd2 & ",""Qualifies"")"
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=COUNTIF(J66:J" & ListEnd2 & ",""No"")"
coop123