Recently I was given the task of extracting a chart of accounts (COA) from a general ledger report. There are many ways I could have done this. Using Auto Filter comes to mind. Being me, I chose to write a small program in Visual Basic for Applications (VBA). In this post I will walk you through the design and code of the program I made.
Design:
First we need to determine the goals for our output or in this case the chart of accounts. I needed a report with:
- account number
- account description
In the source file we see that the account number is in Column A and the account descriptions are in Column E. We also see that rows that begin with a number are the rows that have the account number/description. Since that’s all we need we’ll ignore everything else.
With that information we know that our solution will have to do the following:
- find or determine the first and last rows on the source worksheet
- examine each row in the source worksheet to see if it starts with a number
- if it does start with a number then
- take the account number and
- take the account description and
- write them to the destination sheet
- lastly increment the row in the destination worksheet
It’s obvious that we need a loop in this case we’ll use a FOR loop to examine each row of the source worksheet. It’s also clear we’ll need an IF statement to determine if the first character of the row is a number or not.
Code:
Sub ExtractCOA()
'This program will extract the chart of accounts from a Yardi 7
'default General Ledger (analytics) report run to Excel
'Derek Harlan April 2018 - Licensed in the Public Domain
'Setup Object Variables for the worksheets
'src will be the Source worksheet
Dim src As Worksheet
'dest will be the Destination worksheet
Dim dest As Worksheet
'Setup some counter variables
'firstRow / lastRow will be the first and last rows of the Source workbook
'nextRow will be the row to write in the Destination workbook
'i will be the counter in the For loop
Dim startRow as Long
Dim lastRow As Long
Dim i As Long
Dim nextRow As Long
'Set the initial values for the variables
Set src = Sheets("Report1")
Set dest = Sheets("Sheet1")
startRow = 6 ' this is easy to determine so I just set it the row number
'Find the last row by searching the workbook from the bottom row up
'and assign it to the variable
lastRow = src.Range("A1048576").End(xlUp).Row
i = 0
nextRow = 2 'Row 1 will be the headers in the worksheet so Row 2 is where the data starts
'We'll loop through each row between the first and last rows of the Source workbook
For i = startRow To lastRow Step 1
'If the first character of the value of the cell in A and the current row (i) is a number then
'that row has the account number and name on it
If IsNumeric(Left(src.Range("A" & i).Value, 1)) Then
'set the values in the destination worksheet from the values on the
'current row of the source worksheet
dest.Range("A" & nextRow).Value = _
src.Range("A" & i).Value
dest.Range("B" & nextRow).Value = _
src.Range("E" & i).Value
'lastly increment the nextRow in the destination worksheet
nextRow = nextRow + 1
End If
Next i
End Sub
Running it creates this output –
With both of the requirements met I can say this was a success!
This project took about 20 minutes, from start to finish. In this instance I’m not sure if I saved any time however should I need to do this again I already have the program ready. When evaluating alternatives think about how often you’ll be asked to do this. It may take time to come up with the initial program but once you have it, you see a colossal increase in productivity!
Do you want to get started with VBA? Or want to learn what a FOR loop is?
Check out my book for more uses of Excel VBA in your daily work:
Beginning Microsoft Excel VBA Programming for Accountants
available on Amazon, Apple iBooks and other eBook retailers!
Questions or comments? Connect with me on