Creating a Chart of Accounts from a General Ledger in Microsoft Excel using VBA

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.

General Ledger
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 –

Chart of Accounts in Excel

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

Facebook https://www.facebook.com/DerekHarlanWriter/

Twitter https://twitter.com/tazbirdinvader

%d bloggers like this: