count the number of cells (in a range) greater than a cell value

[Flavien]

Hello!

I want to count the number of cells greater than the value of a cell but either the counter returns 0 or the number of cells in the range.

Please, Can someone help me?

Sub CountCellsWithText()

    Dim cellCount As Integer
    Dim cell As Range
    Dim count As Integer
    Dim k As Integer
    

 count = 0
 k = 0

    For Each cell In Range("I12:K12")
        If Cells(cell).Value > Range("G12") Then
        
        k = k + 1
        
        End If
        
    count = count + 1
    
    Next
   
    MsgBox k & "-" & count
    

End Sub

 

[LazyBug]

Hello!
Try this:

Sub CountCellsWithText()
Dim cell As Range, k As Integer
    For Each cell In Range("I12:K12")
        If cell.Value > Range("G12").Value Then k = k + 1
    Next
    MsgBox k
End Sub

 

[RoryA]

No real need for a loop there:

Msgbox Application.Countif(Range("I12:K12"), ">" & Range("G12").Value)

 

[Joe4]

Kind of an off name "CountCellsWithText", when it looks like you are performing a mathematical computation, and not checking for text at all.

But why are you using VBA instead of just the COUNTIF function?
And loops are very inefficient in VBA, and should be avoided if there are better alternatives.
You can use the COUNTIF right in VBA and avoid the loop.

EDIT: Looks like Rory and I have the same idea!

 

[Flavien]

Hello, thank you for your help.

I have to use VBA, because the number of columns will change as the operator checks are carried out. So, I need the code could tell me how many parts are out of tolerance.

 

[Joe4]

I have to use VBA, because the number of columns will change as the operator checks are carried out.

How are you doing that now? In the code in your initial post, it is not dynamic. Your columns are hard-coded.
Regardless of how that is done, you can still avoid using the loops by using the COUNTIF function in VBA, like Rory showed you.
If you have dynamic ranges, they can easily be dropped/inserted into that code.

 

[Flavien]

Thank you for your help. I don't understand why, countif doesn't work for me.
=> Maybe I need to declare variables beforehand? If so, what type?

On the other hand, LazyBug's code works perfectly. But the program is a bit slow
=> Is there a trick to speed it up?

 

[RoryA]

I don't understand why, countif doesn't work for me

Without seeing what you tried, and knowing what the outcome was, we can't really say.

 

[Alex Blakenburg]

I am sure the others can give you a better way if you provide them with better visibility over your data and what you are trying to do but in relation to you other question see if this helps.

On the other hand, LazyBug's code works perfectly. But the program is a bit slow
=> Is there a trick to speed it up?

Sub CountCellsWithText()
    Dim arr As Variant, k As Long
    Dim i As Long, j As Long
    Dim Threshold As Currency
    
    Threshold = Range("G12").Value
    arr = Range("I12:K12").Value
    
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            If arr(i, j) > Threshold Then k = k + 1
        Next j
    Next i
    
    MsgBox k
End Sub

 

[Flavien]

Thank you for your help.
It's late and the working week was quite long, so I'll try your code on Monday.

I thought I had found a solution that would allow me to determine if each piece is good and calculate the total number of measurements right for each line.
But when I do a new test, I realize that the macro doesn't work properly.

The value of 1.004 is "NOK" while 1.003 is "OK". It's enough to drive me crazy! (cf pictures).

Here is my code, if you see anything that escapes me.

Sub Compter_Nb_Pcs_OK_KO() '18/04/2024


Dim i As Long, j As Long
Dim rng As Range, rng2 As Range
Dim c As Range
Dim Col_Count As Integer, Row_Count As Integer, result As Integer
Dim H As Integer
Dim Col As Integer
Dim val As Variant
Dim k As Integer, l As Integer, m As Integer, n As Integer


Application.ScreenUpdating = False

'   Compter le nombre de repères inscris dans le tableau
    Row_Count = WorksheetFunction.CountIf(Range("A12:A21"), "*")
    
'   Compter le nombre de colonnes intitulées "Pièce X"
    Col_Count = Range(Range("Cote_Maxi").Offset(i, 1), Range("mini").Offset(i, -1)).Count

  
'   Boucle de la première à la colonne "Col_Count" et de la première ligne à la ligne "Row_Count"



For j = 1 To Col_Count
    k = 0
    l = 0
    m = 0
    n = 0
    
  
For i = 1 To Row_Count

Set rng2 = Range("Cote_Maxi").Offset(i, j)
    For Each c In rng2
    
'   Vérifier si la valeur de la cellule est numérique
val = c.Value
 
If c = "" Then GoTo cellulesuivante:

    If IsNumeric(val) = True Then
    
'   Compter le nombre de cellules hors tolérance
    If c.Value < Range("Cote_mini").Offset(i, j).Value Then k = k + 1

    If c.Value > Range("Cote_Maxi").Offset(i, j).Value Then l = l + 1
    
Else

'   Compter le nombre de cellules hors tolérance
    If c.Value = "KO" Or c.Value = "NOK" Then n = n + 1

End If

result = k + l + n


Range("Résultat").Offset(0, j) = result



    Next c
    
    
Next i

If result = Row_Count Then Range("Résultat").Offset(0, j) = "OK"
If result <> Row_Count Then Range("Résultat").Offset(0, j) = "NOK"
'If result = Row_Count Then Range("Résultat").Offset(0, j).Interior.ColorIndex = 4 'vbgreen
If result <> Row_Count Then Range("Résultat").Offset(0, j).Interior.ColorIndex = 3 'vbred

cellulesuivante:

Next j

Application.ScreenUpdating = True



End Sub