# Macro for Reconciling Bank Statement

#### Gaura215

##### Board Regular
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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 />

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

Perfect.

Thanks once again.

Replies
3
Views
73
Replies
0
Views
203
Replies
11
Views
333
Replies
9
Views
350
Replies
18
Views
482

1,203,326
Messages
6,054,742
Members
444,748
Latest member
knowak87

### 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.

### Which adblocker are you using?

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

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