127|EXCEL&VBA – P&L Prep

Challenges
, , , , ,
1

BYU Student Author: @Spencer
Reviewers: @Mark, @Marco, @klayton
Estimated Time to Solve: 25 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
As a new hire at a small accounting firm, your task is to prepare the P&L statement for one of your clients, the “Cookie Company”. All P&L data has been correctly accounted for and placed in the statement. However, there are some accounts with zero balances that need to be removed. Your task is to remove these accounts using a flexible VBA macro (so that next month, the task can be completed by simply clicking button).

Instructions
Your VBA macro must do the following:

  1. Duplicate the “P&L” tab and rename it “Final P&L” (we don’t want to write over the original P&L document).
  2. Within the “Final P&L”, iterate through each account. If the value of the account is 0, delete the entire row.
  3. Once all zero-balance accounts have been deleted, hide column F (column F contains random notes).
  4. After these tasks are complete, have a message box pop up that says “The Final P&L is ready for review. ____ accounts have been removed.” Fill in the “____” with the number of accounts/rows your macro removed.
  5. Place the macro inside the green box in the “Macro” tab. This will create a button you can use to run your macro.

Data Files

Suggestions and Hints
  • If you keep receiving a “Type mismatch (Error 13)” while iterating through the account balances, try using an “If IsNumeric()” statement.
  • To use an integer variable in a message box, you will need to place the variable inside the “CStr()” function.

Solution

Your macro should have deleted 12 accounts/rows.

Solution Image

Challenge127_Solution_Image
Challenge127_Solution_Image1046×688 101 KB

Challenge127_Solution.xlsm
Solution Video: Challenge 127|EXCEL&VBA – P&L Prep

2

image
image850×712 11.9 KB

This was a great challenge, and helped dust off some of the VBA I learned in IS 201. I used ChatGPT for help where I got stuck (which was quite a few places), and got my macro to work, using a “For Each” loop instead of a “Do Until” loop. Thanks for the challenge Spencer!

3

Sub PrepareFinalPnL()
Dim wsOriginal As Worksheet
Dim wsFinalPnL As Worksheet
Dim lastRow As Long
Dim i As Long
Dim removedCount As Long

' Reference to original P&L worksheet
Set wsOriginal = ThisWorkbook.Sheets("P&L")

' Create a copy of original P&L and name it "Final P&L"
wsOriginal.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set wsFinalPnL = ActiveSheet
wsFinalPnL.Name = "Final P&L"

' Find last row in Final P&L
lastRow = wsFinalPnL.Cells(wsFinalPnL.Rows.Count, "A").End(xlUp).Row

' Iterate through each account
For i = lastRow To 2 Step -1
    If wsFinalPnL.Cells(i, 2).Value = 0 Then ' Check if value is 0 in column B (assuming account balances are in column B)
        wsFinalPnL.Rows(i).Delete
        removedCount = removedCount + 1
    End If
Next i

' Hide column F
wsFinalPnL.Columns("F").Hidden = True

' Show message box
MsgBox "The Final P&L is ready for review. " & removedCount & " accounts have been removed."

End Sub