Balance Sheet Generate Table via VBA/Macros

G

gibson8

New Member
Joined
Jan 25, 2024
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Dear Guys,

I am just start learning VBA from the beginner and really frustrating on convert excel formulas to VBA.

I would like to Generate a "Consolidated Balance Sheet" table (right side) from the raw Balance Sheet data on the left via VBA.

Is it possible to do that?

excel forum.jpg


I also attached the excel spreadsheet for the references.

I'm extremely appreciate if someone can help.

Sample.xlsx
BCDEFGH
2Balance SheetConsolidated Balance Sheet ABC Ltd. ABC Ltd.
3Current AssetsHKD HK HK
4Petty Cash$ 53.20ASSETS HKD USD
5HSBC HKD Current Account1,500.00
6HSBC HKD Saving Account252.00Current Assets
7HSBC USD Saving Account3,444.00Cash at Bank5,249673
8Management Fee Receivable5,500.00Receivable39,0155,002
9Commission Receivable33,402.00Prepaid Expenses35646
10Accounts Receivable112.80Rental Deposit50,0006,410
11Prepaid Expenses356.00Due from Others12,1401,556
12Due from Subsidary11,152.78Current Assets106,76013,687
13Due from Directors987.00
14Rental50,000.00Tax Assets
15Investment65,500.00Tax Refund11,1351,428
16Tax Refund11,135.0011,1351,428
17
18Total Current Assets183,394.78Investments65,5008,397
19
20Total:183,39523,512
21check-
Sheet1
Cell Formulas
RangeFormula
G7G7=SUM(SUMIF($B$3:$B$78,{"*HKD*","*USD*","*SGD*","*CHF*","*Cash*"},$C$3:$C$78))
H7:H11,H18,H15H7=G7/7.8
G8G8=SUM(SUMIF($B$3:$B$80,{"*Receivable*"},$C$3:$C$80))
G9G9=SUM(SUMIF($B$3:$B$80,{"*Prepaid*"},$C$3:$C$80))
G10G10=SUM(SUMIF($B$3:$B$80,{"*Rental*"},$C$3:$C$80))
G11G11=SUM(SUMIF($B$3:$B$80,{"*Due from*"},$C$3:$C$80))
F12F12=F6
G12:H12G12=SUM(G7:G11)
G15G15=SUM(SUMIF($B$3:$B$80,{"*Tax*"},$C$3:$C$80))
G16:H16G16=SUM(G15)
C16C16=C15*0.17
D18D18=SUBTOTAL(9, C4:C17)
G18G18=SUM(SUMIF($B$3:$B$80,{"*Investment*"},$C$3:$C$80))
G20:H20H20=H16+H12+H18
G21G21=G20-D18
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
In your VBA code, you can create formulas using the 'Range.Formula' syntax.

If you write it referring to the top left of your range, then Excel will take care of the relative and absolute cell references for you.

For example, in your sheet in cells G12:H12 is '=SUM(G7:G11)'.

The line of code to use in VBA is:
VBA Code: 
Range("G12:H12").Formula = "=SUM(G7:G11)"

When you run that, you will find that G12 contains =SUM(G7:G11) and H12 contains =SUM(H7:H11).

If you want it to evaluate the formula and just put the number in the relevant cell, you can use
VBA Code: 
Range("G12:H12").Formula = Evaluate("=SUM(G7:G11)")

Regards

Murray
 
Upvote 0
myall_blues said:
In your VBA code, you can create formulas using the 'Range.Formula' syntax.

If you write it referring to the top left of your range, then Excel will take care of the relative and absolute cell references for you.

For example, in your sheet in cells G12:H12 is '=SUM(G7:G11)'.

The line of code to use in VBA is:
VBA Code: 
Range("G12:H12").Formula = "=SUM(G7:G11)"

When you run that, you will find that G12 contains =SUM(G7:G11) and H12 contains =SUM(H7:H11).

If you want it to evaluate the formula and just put the number in the relevant cell, you can use
VBA Code: 
Range("G12:H12").Formula = Evaluate("=SUM(G7:G11)")

Regards

Murray
Click to expand...
Thank you for your advise, Murray. I appreciate your help.

My intention is Column F:H is empty before i run the VBA.
After i run the macro, it will generate "Consolidated Balance Sheet" table with red headings,
and also description and items (Column F) and other formulas (Column G:H) which are shown in the pic above.

How to write vba code for creating table?
 
Upvote 0
Here is some code to get you started. You should be able to see from this how to build the required headings and formulas and be able to finish it off.

When you're just starting with VBA and want to learn how to add headings with colours etc it helps to use the macro recorder to give you the basics and then modify the code produced by that as a starting point.

Regards

Murray

VBA Code: 
Sub ConBalSheet()
    Dim FormulaText As String
    '
    ' Add headings
    '
    Range("F2").Value = "Consolidated Balance Sheet"
    Range("G2").Value = "ABC Ltd."
    Range("H2").Value = "ABC Ltd."
    Range("G3").Value = "HK"
    Range("H3").Value = "HK"
    Range("F4").Value = "ASSETS"
    Range("G4").Value = "HKD"
    Range("H4").Value = "USD"
    Range("F6").Value = "Current Assets"
    Range("F7").Value = "Cash at Bank"
    Range("F8").Value = "Receivable"
    '
    ' Format headings
    '
    With Range("F2:H2")
        With .Font
            .Name = "Aptos Narrow"
            .FontStyle = "Bold"
            .Size = 10
            .Color = RGB(255, 255, 255)
        End With
        With .Interior
            .Pattern = xlSolid
            .Color = RGB(165, 0, 33)
        End With
    End With
    With Range("F4:H4")
        With .Font
            .Name = "Aptos Narrow"
            .Size = 10
            .Color = RGB(0, 0, 0)
        End With
        With .Interior
            .Pattern = xlSolid
            .Color = RGB(184, 211, 239)
        End With
    End With
    Range("F4:F6").Font.FontStyle = "Bold"
    '
    ' Add formulas. Formulas such as in G7:G11, G15, G18 are complex with embedded quotes
    ' so easiest to build in parts
    '
    FormulaText = "=SUM(SUMIF($B$3:$B$78,{" & """" & "*HKD*" & """" & ","
    FormulaText = FormulaText & """" & "*USD*" & """" & ","
    FormulaText = FormulaText & """" & "*SGD*" & """" & ","
    FormulaText = FormulaText & """" & "*CHF*" & """" & ","
    FormulaText = FormulaText & """" & "*CASH*" & """" & "},$C$3:$C$78))"
    Range("G7").Formula = FormulaText
    FormulaText = "=SUM(SUMIF($B$3:$B$80,{" & """" & "*Receivable*" & """"
    FormulaText = FormulaText & "},$C$3:$C$80))"
    Range("G8").Formula = FormulaText
    '
    ' Formulas that are the "same" over a range can be done by providing the formula at top left
    '
    Range("H7:H11").Formula = "=G7/7.8"
    '
End Sub
 
Upvote 1
Solution
myall_blues said:
Here is some code to get you started. You should be able to see from this how to build the required headings and formulas and be able to finish it off.

When you're just starting with VBA and want to learn how to add headings with colours etc it helps to use the macro recorder to give you the basics and then modify the code produced by that as a starting point.

Regards

Murray

VBA Code: 
Sub ConBalSheet()
    Dim FormulaText As String
    '
    ' Add headings
    '
    Range("F2").Value = "Consolidated Balance Sheet"
    Range("G2").Value = "ABC Ltd."
    Range("H2").Value = "ABC Ltd."
    Range("G3").Value = "HK"
    Range("H3").Value = "HK"
    Range("F4").Value = "ASSETS"
    Range("G4").Value = "HKD"
    Range("H4").Value = "USD"
    Range("F6").Value = "Current Assets"
    Range("F7").Value = "Cash at Bank"
    Range("F8").Value = "Receivable"
    '
    ' Format headings
    '
    With Range("F2:H2")
        With .Font
            .Name = "Aptos Narrow"
            .FontStyle = "Bold"
            .Size = 10
            .Color = RGB(255, 255, 255)
        End With
        With .Interior
            .Pattern = xlSolid
            .Color = RGB(165, 0, 33)
        End With
    End With
    With Range("F4:H4")
        With .Font
            .Name = "Aptos Narrow"
            .Size = 10
            .Color = RGB(0, 0, 0)
        End With
        With .Interior
            .Pattern = xlSolid
            .Color = RGB(184, 211, 239)
        End With
    End With
    Range("F4:F6").Font.FontStyle = "Bold"
    '
    ' Add formulas. Formulas such as in G7:G11, G15, G18 are complex with embedded quotes
    ' so easiest to build in parts
    '
    FormulaText = "=SUM(SUMIF($B$3:$B$78,{" & """" & "*HKD*" & """" & ","
    FormulaText = FormulaText & """" & "*USD*" & """" & ","
    FormulaText = FormulaText & """" & "*SGD*" & """" & ","
    FormulaText = FormulaText & """" & "*CHF*" & """" & ","
    FormulaText = FormulaText & """" & "*CASH*" & """" & "},$C$3:$C$78))"
    Range("G7").Formula = FormulaText
    FormulaText = "=SUM(SUMIF($B$3:$B$80,{" & """" & "*Receivable*" & """"
    FormulaText = FormulaText & "},$C$3:$C$80))"
    Range("G8").Formula = FormulaText
    '
    ' Formulas that are the "same" over a range can be done by providing the formula at top left
    '
    Range("H7:H11").Formula = "=G7/7.8"
    '
End Sub
Click to expand...
Hi Murray,
Thank you so much for the help.
the FormulaText one is quite new to me. And it is much more flexible by just adding one by one.
I can easily to add/delete new criteria .

I appreciate your spend time and effort to work on this.

Hope you have a great week!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Moving closing balances to opening balance on a new sheet using VBA
Replies
0
Views
371
Derek Armitage
D
J
  • Question
Formula to bring amounts from last month
Replies
4
Views
152
josros60
J
L
  • Solved
help with sumifs multiple Sheets
Replies
7
Views
541
Lukma
L
O
  • Question
calculation amounts for BANK & CASH across sheets based on matching partial item
Replies
0
Views
68
Omran Y
O
J
  • Question
Which formula can I use to automatically generate a new invoice number?
Replies
9
Views
449
mumps
mumps

Forum statistics

Threads
1,215,430
Messages
6,124,851
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top