 (35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

CountIfs with VBA

0

Hello,

I try to use a simple CountIfs formula with a macro which runs w/h error, however the result is not shown(File "Prod" attached)

The code I use:

Sub Completion()
Dim ws1 As Worksheet: Set ws1 = Workbooks("Prod").Worksheets("1")
Dim ws2 As Worksheet: Set ws2 = Workbooks("Prod").Worksheets("2")
Dim lRow As Long, i As Long, c As Long
lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 11 To lRow
If ws1.Cells(i, 1).Value = "PPS" And ws1.Cells(i, 6).Value = "Réalisé" Then
For c = 7 To 60
ws1.Cells(i, c) = _
WorksheetFunction.CountIfs( _
ws2.Range("G:G"), ws1.Cells(7, c), _
ws2.Range("H:H"), ws1.Cells(6, c), _
ws2.Range("A:A"), ws1.Cells(i, 2), _
ws2.Range("B:B"), ws1.Cells(i, 3), _
ws2.Range("C:C"), ws1.Cells(i, 5))
Next c
End If
Next i
MsgBox "Done"
End Sub

0

Sebas.

I think you have two problem- (1) you omitted the file extensions when setting ws1 and ws 2 and (2) your COUNTIFS formula produces a zero result (which is added to the cells but is not displayed).

In the attached file, I've shortened your formula (to have only one ctriteria set for COUNTIFS)- this produces numbers in your sheet "1":

Sub Completion()

Dim ws1 As Worksheet: Set ws1 = Workbooks("Prod.xlsm").Worksheets("1") ' added extension
Dim ws2 As Worksheet: Set ws2 = Workbooks("Prod.xlsm").Worksheets("2") ' added extension
Dim lRow As Long, i As Long, c As Long

lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 11 To lRow
If ws1.Cells(i, 1).Value = "PPS" And ws1.Cells(i, 6).Value = "Réalisé" Then
For c = 7 To 60
ws1.Cells(i, c) = Application.WorksheetFunction.CountIfs( _
ws2.Range("G:G"), ws1.Cells(7, c)) ', _
ws2.Range(Chr(32) & "H:H" & Chr(32)), ws1.Cells(6, c), _
ws2.Range(Chr(32) & "A:A" & Chr(32)), ws1.Cells(i, 2), _
ws2.Range(Chr(32) & "B:B" & Chr(32)), ws1.Cells(i, 3), _
ws2.Range(Chr(32) & "C:C" & Chr(32)), ws1.Cells(i, 5))
Next c
End If
Next i

MsgBox "Done"

End Sub
Suggest you double check the formula and correct code to suit.

Discussion

It was: ws2.Range(Chr(32) & "C:C" & Chr(32)), ws1.Cells(i, 4)   i/o  ws2.Range(Chr(32) & "C:C" & Chr(32)), ws1.Cells(i, 5)

Thanks!
sebas14 (rep: 16) Jan 10, '22 at 10:33 am
Oops! The bits with Chr(32) were from my earlier, failed effort to solve it by writing the COUNTIFS formula in each cell.