Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Jun 07, 2022
Solved

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

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
excel
Macros and VBA
  • hrh_dash's avatar
    hrh_dash
    Jun 07, 2022

    OliverScheurich , 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?

     

     

  • hrh_dash's avatar
    hrh_dash
    Iron Contributor
    Jun 07, 2022

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

      • hrh_dash's avatar
        hrh_dash
        Iron Contributor
        Jun 07, 2022
        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!
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 07, 2022

    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.

    • hrh_dash's avatar
      hrh_dash
      Iron Contributor
      Jun 07, 2022

      OliverScheurich , 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?

       

       

Resources