VBA Code to Populate Balance Sheet from Trial Balance Data

0 votes
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)
Sep 23, 2022 in Others by Kithuzzz
• 38,010 points
888 views

1 answer to this question.

0 votes
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.
answered Sep 24, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How to expend the code to transfer data from one spreadsheet to another based on multiple criteria

 The progress bar is unnecessary. Option Explicit Sub VTest2() ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,700 points
263 views
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,700 points
574 views
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
507 views
0 votes
0 answers

How to handle large http response data from observer in Angular application to avoid browser crash?

Suppose we have a angular application which ...READ MORE

Apr 19, 2019 in Others by Hemant Gajbe
2,440 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
952 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,282 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
575 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
806 views
0 votes
1 answer
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
541 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP