Count the number of times the If statement is true

[owen4512]

Hi all,

I have a cell in sheet1 where i want to store the number of times my if statement is true. In 'Records' i have a value of 0 and i want it so that every time 'Search' matches a record in sheet2 it adds value 1 to the cell 'N13'.

When testing this, i have made sure that multiple items in column 'B' match but the value in 'N13' remains as 1 and wont go any higher.

Any help would be much appreciated

Sub Search()

    Dim i As Integer
    Dim last_row As Integer
    Dim Search As Long
    Dim Records As Long
    
    Search = Sheet1.Range("N11").Value
    Records = Sheet1.Range("N13").Value
    last_row = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))

    
    For i = 2 To last_row
        If Sheet2.Range("B" & i).Value = Search Then
            
            Sheet1.Range("N13").Value = Records + 1
            Sheet1.Range("D11").Value = Sheet2.Range("C" & i).Value
            
        Else
            
        End If
         
   
    Next i


End Sub

 

[AlphaFrog]

Sub Search()

    Dim i As Integer
    Dim last_row As Integer
    Dim Search As Long
    Dim Records As Long
   
    Search = Sheet1.Range("N11").Value
    'Records = Sheet1.Range("N13").Value
    last_row = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))

   
    For i = 2 To last_row
        If Sheet2.Range("B" & i).Value = Search Then
           
            Records = Records + 1
            Sheet1.Range("D11").Value = Sheet2.Range("C" & i).Value
           
        Else
           
        End If
        
   
    Next i
    Sheet1.Range("N13").Value = Records

End Sub

Or you could use a COUNTIF formula

=COUNTIF(Sheet1!B:B, Sheet1!N11)

 

[owen4512]

Sub Search()

    Dim i As Integer
    Dim last_row As Integer
    Dim Search As Long
    Dim Records As Long
  
    Search = Sheet1.Range("N11").Value
    'Records = Sheet1.Range("N13").Value
    last_row = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))

  
    For i = 2 To last_row
        If Sheet2.Range("B" & i).Value = Search Then
          
            Records = Records + 1
            Sheet1.Range("D11").Value = Sheet2.Range("C" & i).Value
          
        Else
          
        End If
       
  
    Next i
    Sheet1.Range("N13").Value = Records

End Sub

Or you could use a COUNTIF formula

=COUNTIF(Sheet1!B:B, Sheet1!N11)

That's worked great - Thank you