VBA: Countifs with loop

[Abdulhaq]

Hi All,

I'm new to this forum and VBA...

I have a query regarding VBA Countifs and ForLoop.

There are 2 Worksheets :

Worksheet 1 have rawdata and worksheet 2 is where I need the output.

In Worksheet Column(C) have dates and Column(I) have Output, from where I need the count in Sheet 2.

in Worksheet 2 in Column(A2) is start date and Column(B2) is End date. In Column(D:D) there are output of which is need the Count.

So I need a countifs with forloop so that It will take counts of the output till the last row.

I know these functions but unable to get output or I didn't that much of expertise in VBA

Any Suggestions other these are most Welcome

 

[Col Delane]

Hi Abdulhaq, and welcome to the Forum

It is not clear from your post as to what data you are trying to count, what is the criteria, and why you need VBA.
Have you thought about how you may be able to produce the result required without VBA (i.e. using formulas)?

Could you please post:

1. a small portion of your rawdata from Worksheet1
2. what does Worksheet2 look like:
   • are there just two values only in A2 (start date) and B2 (End date) or is there a Start date in column A and End date in column B for multiple rows?
   • what does the output in D:D look like?
3. what are the criteria for the COUNTIFS function/formula?

 

[Abdulhaq]

I've tried writing this vba code but unable to execute..please help.....

Sub Countifs12()

Dim x As Long
N = Sheets("Sheet1").range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count

larow1 = Sheets("Sheet2").range("D" & Rows.Count).End(xlUp).Row
For x = 2 To larow1

Cells(x, "E").Value = Application.WorksheetFunction.Countifs(Sheets("Sheet1").range(Cells(2, "B"), Cells(N, "B")), _
(sheets("Sheet2").range(Cells(x, "D"))).Value , (sheets("Sheet1").range(Cells(2, "A"), Cells(N, "A"))), (sheets("Sheet2").range(">=" & Cells(2, "H").Value, _
(Sheets("Sheet1").range(Cells(2, "A"),Cells(N, "A"))), (sheets("Sheet1"),range("<=" & Cells(2, "I").Value)


Next x

End Sub

 

[Col Delane]

I'd like to help, but need more information as per my first post (#2)

 

[DanteAmor]

The columns of your macro do not match those of your explanation.
But according to your macro, I understand that you are going to compare a data from column B of sheet1 with column D of sheet2.

The above can be like this:

Sub Count_Ifs()
    '
    Dim w1 As Worksheet, w2 As Worksheet
    Dim i As Double, u1 As Double, u2 As Double, iCount As Double
    '
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    '
    u1 = w1.Range("B" & Rows.Count).End(xlUp).Row
    u2 = w2.Range("D" & Rows.Count).End(xlUp).Row
    '
    For i = 2 To u2
        iCount = WorksheetFunction.CountIfs(w1.Range("B2:B" & u1), w2.Cells(i, "D"))
        w2.Cells(i, "E").Value = iCount
    Next
End Sub

And the dates are in column A of sheet 1 and columns H and I of sheet 2.

Then:

Sub Count_Ifs()
    '
    Dim w1 As Worksheet, w2 As Worksheet
    Dim i As Double, u1 As Double, u2 As Double, iCount As Double
    '
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    '
    u1 = w1.Range("B" & Rows.Count).End(xlUp).Row
    u2 = w2.Range("D" & Rows.Count).End(xlUp).Row
    '
    For i = 2 To u2
        fec1 = w2.Cells(i, "H")
        fec2 = w2.Cells(i, "I")
        iCount = WorksheetFunction.CountIfs(w1.Range("B2:B" & u1), w2.Cells(i, "D"), _
            w1.Range("A2:A" & u1), ">=" & Format(w2.Cells(i, "H"), "mm/dd/yyyy"), _
            w1.Range("A2:A" & u1), "<=" & Format(w2.Cells(i, "I"), "mm  /dd/yyyy"))
        w2.Cells(i, "E").Value = iCount
    Next
End Sub

---

But it could also be:

Sub Count_Ifs()
    '
    Dim u1 As Double, u2 As Double
    u1 = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    u2 = Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row
    '
    With Sheets("Sheet2").Range("E2:E" & u2)
        .FormulaR1C1 = "=COUNTIFS(Sheet1!R2C2:R" & u1 & "C2,RC4," & _
            "Sheet1!R2C1:R" & u1 & "C1,"">=""&RC8," & _
            "Sheet1!R2C1:R" & u1 & "C1,""<=""&RC9)"
        .Value = .Value
    End With
End Sub

 

[Abdulhaq]

Dear Dante Amor,

Thank you so much for the reply.

i've tried your 2nd given code.. it's working only for the first row. for the rest list it is giving zero output.

Thanks in Advance.

 

[Abdulhaq]

Thank you so much Dante Amor, little tweaking was required and it's working now....

Thanks a ton.

Really appreciate your effort

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.