CountIf Help

[romelsms1]

hello

on topic: I have the same issue with counting dates...so I use countifs for multiple conditions (including date) and I don't understand why it count only to the 600 line in excel :(


Cnt11_ian = Application.WorksheetFunction.CountIfs _
(Sheets(1).Range("H7:H600" & LastRow1), ">=1/1/2014", Sheets(1).Range("H7:H600" & LastRow1), "<2/1/2014", _
Sheets(1).Range("D7:D600" & LastRow1), Sheets(1).Cells(i, "D"), _
Sheets(1).Range("A7:A600" & LastRow1), Sheets(1).Cells(i, "A")) '



if I count without takeing in account the date it can go to the last row in sheet
Cnt11_ian = Application.WorksheetFunction.CountIfs _
(Sheets(1).Range("D7:D" & LastRow1), Sheets(1).Cells(i, "D"), _
Sheets(1).Range("A7:A" & LastRow1), Sheets(1).Cells(i, "A")) '

anu ideea?

[mancubus]

if LastRow1 is null then Range("H7:H600" & LastRow1) = Range("H7:H600")
if LastRow1 is 5 then Range("H7:H600" & LastRow1) = Range("H7:H6005")



after assigning a value to variable LastRow1 (such as LastRow1 = Cells(Rows.Count, 1).End(xlup).Row)



Range("H7:H" & LastRow1)

[romelsms1]

i don't understand :( why to put an if condition

bellow is my code



Sub Button1_Click()

Dim LastRow1 As Integer

Dim LastRow2_ian As Integer '
Dim i As Integer
Dim Cnt11_ian As Double

Dim Cnt21_ian As Double


Sheets(2).Range("C4:AL33") = Null
LastRow1 = Sheets(1).Cells(Rows.Count, "D").End(xlUp).Row



For i = 1 To LastRow1

LastRow2_ian = Sheets(2).Cells(Rows.Count, "C").End(xlUp).Row

Cnt21_ian = Application.WorksheetFunction.CountIfs _
(Sheets(2).Range("C1:C" & LastRow2_ian), Sheets(1).Cells(i, "D"), _
Sheets(2).Range("D1:D" & LastRow2_ian), Sheets(1).Cells(i, "A"))

Cnt11_ian = Application.WorksheetFunction.CountIfs _
(Sheets(1).Range("H7:H600" & LastRow1), ">=1/1/2014", Sheets(1).Range("H7:H600" & LastRow1), "<2/1/2014", _
Sheets(1).Range("D7:D600" & LastRow1), Sheets(1).Cells(i, "D"), _
Sheets(1).Range("A7:A600" & LastRow1), Sheets(1).Cells(i, "A"))


If Cnt11_ian >= 1 And Cnt21_ian = 0 Then

If Sheets(2).Cells(LastRow2_ian, "C") = "" Then 'cauta prima linie
Sheets(2).Cells(LastRow2_ian + 4, "C") = Sheets(1).Cells(i, "D")
Sheets(2).Cells(LastRow2_ian + 4, "D") = Sheets(1).Cells(i, "A")
Sheets(2).Cells(LastRow2_ian + 4, "E") = Cnt11_ian
Else
Sheets(2).Cells(LastRow2_ian + 1, "C") = Sheets(1).Cells(i, "D")
Sheets(2).Cells(LastRow2_ian + 1, "D") = Sheets(1).Cells(i, "A")
Sheets(2).Cells(LastRow2_ian + 1, "E") = Cnt11_ian
End If
End If
Next

Sheets(2).Range("C29:E29") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP1", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C30:E30") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP2", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C31:E31") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP3", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C32:E32") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP4", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C33:E33") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP5", Sheets(2).Range("E4:E28"))

end sub

[mancubus]

apparently, the first two lines in my post are just an explanation of building a range with a variable and are not a part of a macro.

you should use the line Range("H7:H" & LastRow1) to correct the ranges in your code.

if you are willing to go any further, start your own thread or ask a moderator to split this thread into two and post your workbook.