Expand a financial statement

[Aussiebear]

I am looking to expand a financial statement so that it includes all the dates and the subsequent balance per day. The attached workbook has two tables which shows the data as it will be recieved into Excel (table 1), and how I wish to see the data as a finished project.

This will be a major project for me, so I'm hoping that the forum members will bear with me as I ask a running set of questions. At this stage all I'm chasing is a methodology to insert rows between dates where necessary and have the correct secquence of dates applied.

Any ideas greatly appreciated.

[Bob Phillips]

Option Explicit

Public Sub ProcessData()
Dim i As Long, j As Long
Dim mpLastRow As Long
Dim mpDate As Date
Dim mpNextRow As Long
Dim mpTargetSheet As Worksheet

Set mpTargetSheet = Worksheets("Table2")

With ActiveSheet

mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
mpTargetSheet.Cells.ClearContents
.Range("A1:F1").Copy mpTargetSheet.Range("A1")
mpDate = .Range("A2").Value
mpNextRow = 1
For i = 2 To mpLastRow 'iLastRow to 1 Step -1

If .Cells(i, "A").Value <> "" Then

If DateValue(.Cells(i, "A").Value) <> mpDate Then

For j = mpDate + 1 To DateValue(.Cells(i, "A").Value) - 1

mpNextRow = mpNextRow + 1
mpTargetSheet.Cells(mpNextRow, "A").Value = j
mpTargetSheet.Cells(mpNextRow, "F").Value = mpTargetSheet.Cells(mpNextRow - 1, "F").Value
mpDate = DateValue(.Cells(i, "A").Value)
Next j
End If

mpNextRow = mpNextRow + 1
.Cells(i, "A").Resize(, 5).Copy mpTargetSheet.Cells(mpNextRow, "A")
If .Cells(i + 1, "A").Value = "" Then _
.Cells(i + 1, "C").Copy mpTargetSheet.Cells(mpNextRow, "C")
End If
Next i

mpTargetSheet.Range("F2").Value = .Range("F2").Value
End With

With mpTargetSheet

.Range("F3").FormulaR1C1 = "=R[-1]C+RC[-1]-RC[-2]"
.Range("F3").AutoFill .Range("F3").Resize(mpNextRow - 2)
.Columns(1).NumberFormat = "d mmm yyyy"
.Columns("B:C").AutoFit
.Columns("D:E").NumberFormat = "#,##0.00"
.Cells(mpNextRow + 1, "B").Value = "CLOSING BALANCE"
.Cells(mpNextRow + 1, "F").FormulaR1C1 = "=R[-1]C"

With .Range("A2").Resize(mpNextRow, 6)

.BorderAround LineStyle:=xlContinuous, ColorIndex:=5
.Borders(xlInsideVertical).LineStyle = xlNone
With .Borders(xlInsideHorizontal)

.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With

.Activate
ActiveWindow.DisplayGridlines = False
End With

End Sub

[Aussiebear]

Now I'm really confused... :bug:

Your code does everything that I need, but I don't understand any of it. Would you be so kind as to break it down for me please Bob.

Sadly, If ever I needed convincing that I'm as thick as a brick, this certainly does it for me.

[Aussiebear]

Option Explicit

Public Sub ProcessData()
Dim i As Long, j As Long
Dim mpLastRow As Long
Dim mpDate As Date
Dim mpNextRow As Long
Dim mpTargetSheet As Worksheet

Set mpTargetSheet = Worksheets("Table2")

With ActiveSheet

mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' finds the last row in Column A
mpTargetSheet.Cells.ClearContents
.Range("A1:F1").Copy mpTargetSheet.Range("A1") ' Copies the Column Headers across to Table2 Sheet
mpDate = .Range("A2").Value ' finds the initial starting date
mpNextRow = 1
For i = 2 To mpLastRow 'iLastRow to 1 Step -1 ' Is this comment still relevant?

If .Cells(i, "A").Value <> "" Then

If DateValue(.Cells(i, "A").Value) <> mpDate Then

For j = mpDate + 1 To DateValue(.Cells(i, "A").Value) - 1 ' cannot follow this line. Something to do with building a running date value

mpNextRow = mpNextRow + 1 ' starts the looping process for values in columns A & F
mpTargetSheet.Cells(mpNextRow, "A").Value = j
mpTargetSheet.Cells(mpNextRow, "F").Value = mpTargetSheet.Cells(mpNextRow - 1, "F").Value
mpDate = DateValue(.Cells(i, "A").Value)
Next j
End If

mpNextRow = mpNextRow + 1
.Cells(i, "A").Resize(, 5).Copy mpTargetSheet.Cells(mpNextRow, "A")
If .Cells(i + 1, "A").Value = "" Then _
.Cells(i + 1, "C").Copy mpTargetSheet.Cells(mpNextRow, "C")
End If
Next i

mpTargetSheet.Range("F2").Value = .Range("F2").Value ' sets the initial Balance value for the new sheet
End With

With mpTargetSheet

.Range("F3").FormulaR1C1 = "=R[-1]C+RC[-1]-RC[-2]" ' fomulae for determining the balance at a date
.Range("F3").AutoFill .Range("F3").Resize(mpNextRow - 2)
.Columns(1).NumberFormat = "d mmm yyyy" ' determines the date format
.Columns("B:C").AutoFit ' expands these columns to suit the data string length
.Columns("D:E").NumberFormat = "#,##0.00" ' determines the currency format for columns D & E
.Cells(mpNextRow + 1, "B").Value = "CLOSING BALANCE" ' determines the cell location for the string value "Closing Balance"
.Cells(mpNextRow + 1, "F").FormulaR1C1 = "=R[-1]C"

' sets the format (lines and colour) for the range used by the data
With .Range("A2").Resize(mpNextRow, 6)

.BorderAround LineStyle:=xlContinuous, ColorIndex:=5
.Borders(xlInsideVertical).LineStyle = xlNone
With .Borders(xlInsideHorizontal)

.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With

.Activate
ActiveWindow.DisplayGridlines = False ' turns off the row & column headers
End With

End Sub


Am I close Bob?

[Bob Phillips]

My code has had many effects on many people, but that is a first I have to admit.

It isn't really complex, it is really a standard loop, you just need to break it down bit by bit.

The main loop iterates through all the rows, calculating the last row in the standard way. It assumes that the first data row (row 2) is valid and has a date, and saves that for testing i the loop. Note that the 'comment on the start of the loop is not really a comment, I have a template which includes a forward step and a backwards step, I just forgot to remove the unwanted one.

The loop checks for blank values in column A, it ignores these as they are processed on a valid row.

It then checks the value in column A for the saved date. If it isn't, it creates a number of fill-in rows for each of those missing dates with the following attributes
- the next date
- the previous balance.
It uses Datevalue as the dates in the source data are text, not real dates, so they need transforming.

The row is copied acros together with column C of the next row if the next row is tied to this.

After all rows are copied across with the fill-in dates, the target data is formatted, and a formula for the running balance is inserted.

So, yes, I think you more or less got it, so perhaps you aren't as thick as you think you are?

[Aussiebear]

Well, I had to think about it for a day before I began to understand it. :friends: