Specifying range for COUNTIF

[coop123]

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

 

[6StringJazzer]

You don't explain how you arrived at J66--is that supposed to the cell that resulted from Find? If so, what is B66? Same row but in column B? Let's assume so for this example but you need to confirm.

Assign the result of Find to a Range variable, then use the address of that range.

Also I recommend against selecting cells then using ActiveCell or Selection to refer to them later. It is better to be explicit. I did not go through your code to change this, just something to consider.

Also, if you use CODE tags around your VBA code, the spacing will be preserved and it will be much easier to read.

   Dim Found As Range
   
  
   Set Found = Cells.Find(What:="EHP", _
                          After:=Range("A3"), _
                          LookIn:=xlFormulas2, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
   
   Found.End(xlDown).Select
   ListEnd2 = ActiveCell.Row
   
   Range("J3").Select
   Selection.End(xlDown).Select
   Selection.Offset(6, 0).Select
   
   ActiveCell.Formula = _
   "=COUNTIF(" & Found.Address(False, False) & ":J" & ListEnd2 & ",""Qualifies"")/COUNTA(" & Cells(Found.Row, "B").Address(False, False) & ":B" & ListEnd2 & ")"
   
   Selection.NumberFormat = "0%"
   
   Selection.Offset(1, 0).Select
   ActiveCell.Formula = "=COUNTIF(" & Found.Address(False, False) & ":J" & ListEnd2 & ",""Qualifies"")"
   Selection.Offset(1, 0).Select
   ActiveCell.Formula = "=COUNTIF(" & Found.Address(False, False) & ":J" & ListEnd2 & ",""No"")"

 

[coop123]

Hello

Thanks for you response. The code works perfectly, the assumptions you made about J66 and B66 were correct. I'll take on board you advice about ActiveCell or Selection and using CODE tags.

Thanks again

coop123