Excel VBA Course
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.)

View Course

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
excel
MS Office Version: sebas14
posted by: sebas14 (rep: 16) on Sun Jan 9, 2022 at 1:25 pm
Answer
Discuss

Answers

0
Selected Answer

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.
posted by: John_Ru (rep: 6142) on Mon Jan 10, 2022 at 9:53 am
Discuss

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.

Glad you sorted it. Thanks for selecting my answer, Sebas.
John_Ru (rep: 6142) Jan 10, '22 at 11:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login