Macro for Reconciling Bank Statement

[Gaura215]

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.

 

[Comfy]

Using the information you provided, make sure the two amount are sorted ascending prior to running this:

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 />

 

[Gaura215]

Thanks a ton. This work absolutely the way I wanted.

Perfect.

Thanks once again.