Hello!
I have 2 excel sheet :
- “Total Report”
- “Statistics”
In the sheet “Statistics” I have one column “A3:A10” with a text in each cells (e.g. “A3” = “Alerts”).
In the sheet “Total Report” I have a table, and inside I have 3 ranges: "F2:F3000", "J2:J3000" and "N2:N3000". Theses 3 ranges contains cells with similar values that are present in the column “A3:A10” from the sheet “Statistics”. I need a function which is capable to count how many time values from column A3:A10” from the sheet “Statistics” are present in the 3 ranges: "F2:F3000", "J2:J3000" and "N2:N3000" from the sheet “Total Report”.
I would like to do it in an automatic way. I mean the Macro must be able to pick the text from “A3:A10” and search in the 3 ranges "F2:F3000", "J2:J3000" and "N2:N3000" how many time this term is present.
I did a code, it works, but I want to do it more dynamic because if someone add one value to the list “A3:A10”, the Macro must be able to add this text in the calculs (I don’t want to had new lines in my code when I had a new cell in the list “A3:A10”.
Results of the “countif” must be present on the cells next to the list “A3:A10” like this:
- Column B for Task1
- Column C for Task2
- Column D for Task3
- Then SUM B + C + D in Column E
I can’t merge a the 3 ranges "F2:F3000", "J2:J3000" and "N2:N3000" in one with “Application.Union” but VBA doesn’t want to recognize it.
I tried to write a loop, but I don’t succed.
This is the code I write:
I hope that i'm clear. Thank you for your consideration. Sorry for my English.
I have 2 excel sheet :
- “Total Report”
- “Statistics”
In the sheet “Statistics” I have one column “A3:A10” with a text in each cells (e.g. “A3” = “Alerts”).
In the sheet “Total Report” I have a table, and inside I have 3 ranges: "F2:F3000", "J2:J3000" and "N2:N3000". Theses 3 ranges contains cells with similar values that are present in the column “A3:A10” from the sheet “Statistics”. I need a function which is capable to count how many time values from column A3:A10” from the sheet “Statistics” are present in the 3 ranges: "F2:F3000", "J2:J3000" and "N2:N3000" from the sheet “Total Report”.
I would like to do it in an automatic way. I mean the Macro must be able to pick the text from “A3:A10” and search in the 3 ranges "F2:F3000", "J2:J3000" and "N2:N3000" how many time this term is present.
I did a code, it works, but I want to do it more dynamic because if someone add one value to the list “A3:A10”, the Macro must be able to add this text in the calculs (I don’t want to had new lines in my code when I had a new cell in the list “A3:A10”.
Results of the “countif” must be present on the cells next to the list “A3:A10” like this:
- Column B for Task1
- Column C for Task2
- Column D for Task3
- Then SUM B + C + D in Column E
I can’t merge a the 3 ranges "F2:F3000", "J2:J3000" and "N2:N3000" in one with “Application.Union” but VBA doesn’t want to recognize it.
I tried to write a loop, but I don’t succed.
This is the code I write:
Code:
Public Sub countTaskocc()
Dim Task1 As Range
Dim Task2 As Range
Dim Task3 As Range
Set Task1 = Worksheets("Total Report").Range("F2:F3000")
Set Task2 = Worksheets("Total Report").Range("J2:J3000")
Set Task3 = Worksheets("Total Report").Range("N2:N3000")
For Each cell In Task1
If cell.Value = Range("a3").Value Then
Range("b3") = WorksheetFunction.CountIf(Task1, Range("A3"))
ElseIf cell.Value = Range("a4").Value Then
Range("b4") = WorksheetFunction.CountIf(Task1, Range("A4"))
ElseIf cell.Value = Range("a5").Value Then
Range("b5") = WorksheetFunction.CountIf(Task1, Range("A5"))
ElseIf cell.Value = Range("a6").Value Then
Range("b6") = WorksheetFunction.CountIf(Task1, Range("A6"))
ElseIf cell.Value = Range("a7").Value Then
Range("b7") = WorksheetFunction.CountIf(Task1, Range("A7"))
ElseIf cell.Value = Range("a8").Value Then
Range("b8") = WorksheetFunction.CountIf(Task1, Range("A8"))
ElseIf cell.Value = Range("a9").Value Then
Range("b9") = WorksheetFunction.CountIf(Task1, Range("A9"))
ElseIf cell.Value = Range("a10").Value Then
Range("b10") = WorksheetFunction.CountIf(Task1, Range("A10"))
End If
Next
For Each cell In Task2
If cell.Value = Range("a3").Value Then
Range("c3") = WorksheetFunction.CountIf(Task2, Range("A3"))
ElseIf cell.Value = Range("a4").Value Then
Range("c4") = WorksheetFunction.CountIf(Task2, Range("A4"))
ElseIf cell.Value = Range("a5").Value Then
Range("c5") = WorksheetFunction.CountIf(Task2, Range("A5"))
ElseIf cell.Value = Range("a6").Value Then
Range("c6") = WorksheetFunction.CountIf(Task2, Range("A6"))
ElseIf cell.Value = Range("a7").Value Then
Range("c7") = WorksheetFunction.CountIf(Task2, Range("A7"))
ElseIf cell.Value = Range("a8").Value Then
Range("c8") = WorksheetFunction.CountIf(Task2, Range("A8"))
ElseIf cell.Value = Range("a9").Value Then
Range("c9") = WorksheetFunction.CountIf(Task1, Range("A9"))
ElseIf cell.Value = Range("a10").Value Then
Range("c10") = WorksheetFunction.CountIf(Task1, Range("A10"))
End If
Next
For Each cell In Task3
If cell.Value = Range("a3").Value Then
Range("d3") = WorksheetFunction.CountIf(Task3, Range("A3"))
ElseIf cell.Value = Range("a4").Value Then
Range("d4") = WorksheetFunction.CountIf(Task3, Range("A4"))
ElseIf cell.Value = Range("a5").Value Then
Range("d5") = WorksheetFunction.CountIf(Task3, Range("A5"))
ElseIf cell.Value = Range("a6").Value Then
Range("d6") = WorksheetFunction.CountIf(Task3, Range("A6"))
ElseIf cell.Value = Range("a7").Value Then
Range("d7") = WorksheetFunction.CountIf(Task3, Range("A7"))
ElseIf cell.Value = Range("a8").Value Then
Range("d8") = WorksheetFunction.CountIf(Task3, Range("A8"))
ElseIf cell.Value = Range("a9").Value Then
Range("d9") = WorksheetFunction.CountIf(Task1, Range("A9"))
ElseIf cell.Value = Range("a10").Value Then
Range("d10") = WorksheetFunction.CountIf(Task1, Range("A10"))
End If
Next
Range("e3") = WorksheetFunction.Sum(Range("B3") + Range("c3") + Range("d3"))
Range("e4") = WorksheetFunction.Sum(Range("B4") + Range("c4") + Range("d4"))
Range("e5") = WorksheetFunction.Sum(Range("B5") + Range("c5") + Range("d5"))
Range("e6") = WorksheetFunction.Sum(Range("B6") + Range("c6") + Range("d6"))
Range("e7") = WorksheetFunction.Sum(Range("B7") + Range("c7") + Range("d7"))
Range("e8") = WorksheetFunction.Sum(Range("B8") + Range("c8") + Range("d8"))
Range("e9") = WorksheetFunction.Sum(Range("B9") + Range("c9") + Range("d9"))
Range("e10") = WorksheetFunction.Sum(Range("B10") + Range("c10") + Range("d10"))
Range("B1").EntireColumn.Delete
Range("b1").EntireColumn.Delete
Range("b1").EntireColumn.Delete
End Sub
I hope that i'm clear. Thank you for your consideration. Sorry for my English.