In this article, we are going to learn how to count and display total through VBA in Microsoft Excel.
Let’s understand with a simple exercise, how and where we can count and display totals through VBA in Microsoft Excel.
We have data in Sheet 1 in which column A contains category, column B contains Competitor Id and column C contains status.
Now, we want to retrieve a report in Sheet2 in the same workbook that contains the data of passed and failed candidates, categorically.
Follow below given steps:-
Sub CountStatus() Dim Lastrow As Long, Countpass1 As Long, countfail1 As Long Dim erow As Long, Countpass2 As Long, CountFail2 As Long Lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Countpass1 = 0 countfail1 = 0 Countpass2 = 0 CountFail2 = 0 For i = 2 To Lastrow If Sheet1.Cells(i, 1) = "CTY1" And Sheet1.Cells(i, 3) = "Pass" Then Countpass1 = Countpass1 + 1 ElseIf Sheet1.Cells(i, 1) = "CTY1" And Sheet1.Cells(i, 3) = "Fail" Then countfail1 = countfail1 + 1 ElseIf Sheet1.Cells(i, 1) = "CTY2" And Sheet1.Cells(i, 3) = "Pass" Then Countpass2 = Countpass2 + 1 ElseIf Sheet1.Cells(i, 1) = "CTY2" And Sheet1.Cells(i, 3) = "Fail" Then CountFail2 = CountFail2 + 1 End If Next i 'Msgbox "Pass count of CTY1," & " " & Countpass1 & " " & "Fail Count of CTY1," & " " & countfail1 & vbCrLf & "Pass count of CTY2," & " " & Countpass2 & " " & "Fail Count of CTY2," & " " & CountFail2 Sheet2.Range("A2:C500").Clear Sheet2.Cells(erow, 1) = "CTY1" Sheet2.Cells(erow, 2) = Countpass1 Sheet2.Cells(erow, 3) = countfail1 erow = erow + 1 Sheet2.Cells(erow, 1) = "CTY2" Sheet2.Cells(erow, 2) = Countpass2 Sheet2.Cells(erow, 3) = CountFail2 End Sub
• To run the macro, press the key F5.
• All details will get updated in Sheet2 as per the requirement.
This is the way to count and display totals through VBA in Microsoft Excel.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.