VBA Code to Populate Balance Sheet from Trial Balance Data

Question

I've read for a long time, but this is my first time posting. For a project I'm working on, I need to format Excel trial balance data into a "balance sheet."

Basically, I have the balance sheet template in another sheet and the trial balance data in one spreadsheet ("Data") ("Balance Sheet")

I must add information to the balance sheet from the ("Data") sheet ("Balance Sheet"). I can't seem to get my head around how to do this.

I have two macros: one that sums up each group of accounts, and the other that formats the trial balance data by account number (ex. all cash accounts are summed together on one line in the balance sheet). However, because this code is now hard coded to the data in the balance sheet, I am having difficulties making it reliable and adaptable. How can I make this code more adaptable so that it correctly populates? (for example, if I added another "cash" account to the cash group, it would add that amount to the "cash" line in the balance sheet)

Answer 1

It appears that what you need to do is construct an array for each sort of account, however, I haven't received your code yet. Let's assume you only have arrCash and arrLiability for the sake of simplicity. Then, you would insert each known GL code into the arrays. Alternatively, you may store a list of GL codes on a different spreadsheet. Now for the enjoyable part. Each code in your Excel spreadsheet would be compared to an element in your arrays using a looping algorithm. Add the amount to one of your variables if the comparison yields a true result. If the comparison returns false, write a program that redims the array to which the GL code must be placed before adding the GL code. Adding to the separate spreadsheet, for example. You would need to add that sum to the corresponding variable after adding the new gl code to the array. You would update your balance sheet with the results of the calculations after they were all finished.