SOLVED

Why does my countifs vba returns 0 when the excel formula was previously populating the correct data

Contributor

Why does my countifs vba returns 0 when the excel formula was previously populating the correct data?

 

I am confused on where did it gone wrong.

Sub CalculateCountifs()

    Dim ws As Worksheet
    Dim BSCSWb As Workbook
    Dim BSCSWs As Worksheet
    Dim path As String
    Dim rngA As Range
    Dim rngB As Range
    Dim rngC As Range
    Dim BSCSlastRow As Long
                   
    Set ws = Sheet1
    path = ws.Range("E1").Value
    Set BSCSWb = Workbooks.Open(path)
    Set BSCSWs = BSCSWb.Worksheets("Raw")
    BSCSlastRow = BSCSWs.Cells(BSCSWs.Rows.Count, "B").End(xlUp).Row
    
    Set rngA = BSCSWs.Range("B2:B" & BSCSlastRow)
    Set rngB = BSCSWs.Range("K2:K" & BSCSlastRow)
    Set rngC = BSCSWs.Range("J2:J" & BSCSlastRow)
    
    ThisWorkbook.Activate
    
    ws.Range("C66") = WorksheetFunction.CountIfs(rngA, ws.Range("C15"), rngB, "GIRO", rngC, "EBS") + WorksheetFunction.CountIfs(rngA, Range("C15"), rngB, "Credit Card", rngC, "EBS")
    ws.Range("C67") = WorksheetFunction.CountIfs(rngA, ws.Range("C15"), rngB, "GIRO", rngC, "BSCS") + WorksheetFunction.CountIfs(rngA, Range("C15"), rngB, "Credit Card", rngC, "BSCS")
     
    'BSCSWb.Close
    
    Set rngA = Nothing
    Set rngB = Nothing
    Set rngC = Nothing
 
    End Sub
5 Replies

@hrh_dash 

Sub countifs()

Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim ws As Worksheet
Dim maxrow As Integer

Set ws = ActiveSheet

maxrow = Range("B" & Rows.count).End(xlUp).Row

    Set rngA = Range(Cells(3, 2), Cells(maxrow, 2))
    Set rngB = Range(Cells(3, 10), Cells(maxrow, 10))
    Set rngC = Range(Cells(3, 11), Cells(maxrow, 11))
    
    ws.Range("C66") = WorksheetFunction.countifs(rngA, ws.Range("C15"), rngB, "GIRO", rngC, "EBS") _
                    + WorksheetFunction.countifs(rngA, ws.Range("C15"), rngB, "Credit Card", rngC, "EBS")
    ws.Range("C67") = WorksheetFunction.countifs(rngA, ws.Range("C15"), rngB, "GIRO", rngC, "BSCS") _
                    + WorksheetFunction.countifs(rngA, ws.Range("C15"), rngB, "Credit Card", rngC, "BSCS")
     
End Sub

You can try these lines of code to calculate countifs.

best response confirmed by hrh_dash (Contributor)
Solution

@Quadruple_Pawn , thank you for assisting. I am looking up data from another workbook from a different file path.

 

Tried the code that you have provided; had came up with an error:
Run-time error '6' Overflow.

 

Are you able to code in a way whereby data is look up from another workbook from a different file path?

 

 

@Hans Vogelaar , sorry to bother you again, would you be able to help take a quick look how the code should be amended?

Went to take a relook at the data again and the code, restart the excel and everything seems to be populating the correct figures. thanks for the help again!