Jun 07 2022 03:39 AM - edited Jun 07 2022 06:25 AM
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
Jun 07 2022 05:17 AM
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.
Jun 07 2022 05:54 AM
Solution@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?
Jun 07 2022 06:28 AM
@Hans Vogelaar , sorry to bother you again, would you be able to help take a quick look how the code should be amended?
Jun 07 2022 07:23 AM
I'd have to see the workbooks involved.
Jun 07 2022 08:57 AM
Jun 07 2022 05:54 AM
Solution@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?