Macro for Reconciling Bank Statement

Gaura215

Board Regular
Joined
Feb 2, 2011
Messages
97
Hello
I want to write a macro for doing bank reconciliation statement. I have to do this every month.

I have my details from bank book from coloum "A to E" and details from cash book from coloum "G to I". Both of these data have been sorted with amounts in increasing order. Amount are in coloum E & I respectively for bank and cash book.

I want to write a macro which will keep the amount sorted in increasing order, but will bring the same amount in the same rows.

Please someone help me.

Thanks in advance for all advices/help to all excel/vba gurus.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Using the information you provided, make sure the two amount are sorted ascending prior to running this:

Code:
Sub FormatBR()
    Dim rBank As Range, rCash As Range
    Dim i As Long
    
    Set rBank = Range(Cells(1, 1), Cells(Range("E65536").End(xlUp).Row, 5))
    Set rCash = Range(Cells(1, 7), Cells(Range("I65536").End(xlUp).Row, 9))
    
    For i = 1 To rBank.Rows.Count + rCash.Rows.Count
    If Cells(i, 9).Value = "" Or Cells(i, 5).Value = "" Then Exit Sub
        Select Case Cells(i, 5).Value
            Case Is < Cells(i, 9).Value
                rCash.Cut Cells(i + 1, 7)
                Set rBank = Range(Cells(i + 1, 1), Cells(Range("E65536").End(xlUp).Row, 5))
            Case Is > Cells(i, 9).Value
                rBank.Cut Cells(i + 1, 1)
                Set rCash = Range(Cells(i + 1, 7), Cells(Range("I65536").End(xlUp).Row, 9))
            Case Else
                Set rBank = Range(Cells(i + 1, 1), Cells(Range("E65536").End(xlUp).Row, 5))
                Set rCash = Range(Cells(i + 1, 7), Cells(Range("I65536").End(xlUp).Row, 9))
        End Select
    Next i
End Sub

My testing data:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">65</td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">98</td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">651</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">651</td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">32151</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">4165</td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">58136</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">9874</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">191981</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Was converted to:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">98</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">651</td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">651</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">4165</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">9874</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">32151</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">p</td><td style=";">o</td><td style="text-align: right;;">58136</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">q</td><td style=";">w</td><td style=";">e</td><td style=";">r</td><td style="text-align: right;;">191981</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
 
Upvote 0

Forum statistics

Threads
1,224,386
Messages
6,178,285
Members
452,835
Latest member
ExcelNerd24

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