How do I set up a macro to help me to do bank reconciliations?

J

Jaime

Hello there

I used to have a macro in Excel 4 that compare 2 databases ,lets say bank
statements and outstandings checks. Macro sorted them, if check # was not
the same inserted a row the final output was total outstanding checks.
I'm talking more than 10 years ago!

Please help!
 
J

JLGWhiz

I revised the program so that it will put up the graphics by code and I can
now post it here.
To run it when the workbook opens, paste this into the ThisWorkbook code
module:

Private Sub Workbook_Open()
Range("H8").Interior.ColorIndex = xlColorIndexNone
mainBB
End Sub


The code below is to be pasted into the standard code module1.

Sub bnkBal()
DisplayAlerts = True
Worksheets(1).Activate
START:
stmtBal = Application.InputBox("ENTER THE CLOSING BALANCE ON THE BANK
STATEMENT.", "BANK BALANCE", Left:=500, Top:=175, Type:=1)

If stmtBal = False Then
MsgBox "MUST BE NUMERIC ENTRY", , "INVALID ENTRY"
cyc = MsgBox("DO YOU WANT TO RE-ENTER DATA?", vbYesNo +
vbQuestion, "CONTINUE?")
If cyc = vbNo Then
GoTo FINISH:
ElseIf cyc = vbYes Then
Range("$B$1").ClearContents
GoTo START:
End If
End If
Range("$B$1") = stmtBal
Do
regBal = Application.InputBox("ENTER THE BALANCE FROM THE CHECK
REGISTER.", "REGISTER BALANCE", Left:=500, Top:=175, Type:=1)
If regBal = False Then
MsgBox "MUST BE NUMERIC ENTRY", , "INVALID ENTRY"
cyc = MsgBox("DO YOU WANT TO RE-ENTER DATA?", vbYesNo +
vbQuestion, "CONTINUE?")
If cyc = vbNo Then
GoTo FINISH:
End If
End If
Loop While regBal = False
Range("$E$1") = regBal
chkInprcs = MsgBox("ARE THERE ANY OUTSTANDING CHECKS TO ENTER?",
vbYesNo, "CHECKS IN PROCESS")
If chkInprcs = vbYes Then
Counter = 7
Do
Do
osChks = Application.InputBox("ENTER THE AMOUNT FOR A
CHECK WRITTEN BUT NOT ON THE BANK STATEMENT.", "OUTSTANDING CHECKS",
Left:=500, Top:=175)
If Not IsNumeric(osChks) Or osChks = False Then
MsgBox "MUST BE NUMERIC ENTRY", , "INVALID
ENTRY"
osChks = False
cyc = MsgBox("DO YOU WANT TO CONTINUE?", vbYesNo
+ vbQuestion, "CONTINUE?")
If cyc = vbNo Then
GoTo FINISH:
End If
End If
Loop While cyc = vbYes And osChks = False
Range("$B$" & Counter) = osChks
Response = MsgBox("IS THERE ANOTHER CHECK TO ENTER?",
vbYesNo, "OUTSTANDING CHECKS")
If Response = vbYes Then
Counter = Counter + 1
End If
Loop Until Response = vbNo Or Counter = 14
End If
eWthdwl = MsgBox("ARE THERE ELECTRONIC WITHDRAWALS TO ENTER?", vbYesNo,
"ELECTRONIC WITHDRAWALS")
If eWthdwl = vbYes Then
Counter = 7
Do
Do
eChks = Application.InputBox("ENTER THE AMOUNT FOR A
CHECK OR WITHDRAWAL THAT IS NOT IN THE CHECK REGISTER.", "ELECTRONIC
WITHDRAWALS", Left:=500, Top:=175)
If Not IsNumeric(eChks) Or eChks = False Then
MsgBox "MUST BE NUMERIC ENTRY", , "IVALID ENTRY"
eChks = False
cyc = MsgBox("DO YOU WANT TO CONTINUE?", vbYesNo
+ vbQuestion, "CONTINUE?")
If cyc = vbNo Then
GoTo FINISH:
End If
End If
Loop While cyc = vbYes And eChks = False
Range("$E$" & Counter) = eChks
Response = MsgBox("IS THERE ANOTHER WITHDRAWAL TO ENTER?",
vbYesNo, "OUTSTANDING CHECKS")
If Response = vbYes Then
Counter = Counter + 1
End If
Loop Until Response = vbNo Or Counter = 14
End If
oddDepst = MsgBox("ARE THERE ANY UNPOSTED DEPOSITS THAT WERE MADE AFTER
THE CLOSING DATE?", vbYesNo, "RECENT DEPOSITS")
If oddDepst = vbYes Then
Counter = 16
Do
Do
unpstDpst = Application.InputBox("ENTER THE AMOUNT OF A
DEPOSIT YOU MADE THAT IS NOT ON THE STATEMENT.", "UNPOSTED DEPOSITS",
Left:=500, Top:=175)
If Not IsNumeric(unpstDpst) Or unpstDpst = False
Then
MsgBox "MUST BE NUMERIC ENTRY", , "INVALID
ENTRY"
unpstDpst = False
cyc = MsgBox("DO YOU WANT TO CONTINUE?", vbYesNo
+ vbQuestion, "CONTINUE?")
If cyc = vbNo Then
GoTo FINISH:
End If
End If
Loop While cyc = vbYes And unpstDpst = False
Range("$B$" & Counter) = unpstDpst
Response = MsgBox("IS THERE ANOTHER UNPOSTED DEPOSIT TO
ENTER?", vbYesNo, "UNPOSTED DEPOSITS")
If Response = vbYes Then
Counter = Counter + 1
End If
Loop Until Response = vbNo Or Counter = 19
End If
eDpst = MsgBox("ARE THERE ANY ELECTRONIC DEPOSITS NOT IN THE REGISTER TO
ENTER?", vbYesNo, "ELECTRONIC DEPOSITS")
If eDpst = vbYes Then
Counter = 16
Do
Do
dirDpst = Application.InputBox("ENTER THE AMOUNT OF
DIRECT DEPOSIT NOT IN THE REGISTER.", "DIRECT DEPOSIT", Left:=500, Top:=175)
If Not IsNumeric(dirDpst) Or dirDpst = False Then
MsgBox "MUST BE NUMERIC ENTRY", , "INVALID
ENTRY"
dirDpst = False
cyc = MsgBox("DO YOU WANT TO CONTINUE?", vbYesNo
+ vbQuestion, "CONTINUE?")
If cyc = vbNo Then
GoTo FINISH:
End If
End If
Loop While cyc = vbYes And dirDpst = False
Range("$E$" & Counter) = dirDpst
Response = MsgBox("IS THERE ANOTHER DIRECT DEPOSIT TO
ENTER?", vbYesNo, "DIRECT DEPOSITS")
If Response = vbYes Then
Counter = Counter + 1
End If
Loop Until Response = vbNo Or Counter = 19
End If

CHECKFIX:
Range("$H$4") = Cells(4, 2) - Cells(4, 5)
If Range("H4").Value = 0 Then
Range("H4").Interior.ColorIndex = xlColorIndexNone
Range("H4").ClearContents
ElseIf Range("H4").Value > 0 Then
Range("H4").Interior.ColorIndex = 6
ElseIf Range("H4").Value < 0 Then
Range("H4").Interior.ColorIndex = 3
End If
If Range("$B$4") <> Range("$E$4") Then
MsgBox "Error amount = " & FormatCurrency(Cells(4, 8).Value, ,
, -2), vbExclamation, "THERE IS AN ERROR"
makeCorr = InputBox("ENTER THE RANGE TO CORRECT USING A1 FORMAT.
DO NOT USE QUOTATION MARKS.", "RANGE TO CORRECT", xpos:=10000, ypos:=5000)
corrEntry = Application.InputBox("ENTER THE CORRECT AMOUNT FOR
RANGE(" & makeCorr & "). DO NOT USE DOLLAR SIGN.", "CORRECTED ENTRY",
Left:=500, Top:=175, Type:=1)
Range(makeCorr) = corrEntry
GoTo CHECKFIX:
Else
MsgBox "Good Balance", vbInformation, "BALANCED"
End If
recap = MsgBox("Do you want to clear the worksheet?", vbYesNo, "CLEAR
WORKSHEET?")
If recap = vbYes Then
Range("$B$1, $E$1, $B$7:$B$13, $E$7:$E$13, $B$16:$B$18,
$E$16:$E$18").ClearContents
End If
Cls = MsgBox("ARE YOU FINISHED?", vbYesNo + vbQuestion, "CONTINUE?")
If Cls = vbNo Then
GoTo START:
Else
FINISH:
ActiveWorkbook.Saved = True
Application.DisplayAlerts = False
Application.Quit
End If
End Sub
Sub mainBB()
mkFormBBtext
mkFormBBlines
mkFormBBfmla
bnkBal
Sheets(1).Cells.Clear
End Sub
Sub mkFormBBtext()
With Sheets(1)
Range("A1:E1").EntireColumn.ColumnWidth = 20
Columns("C").ColumnWidth = 1.75
Columns("H").ColumnWidth = 11
End With
With Range("A1")
.Value = "Bank Balance"
.Font.Bold = True
End With
With Range("D1")
.Value = "Register Balance"
.Font.Bold = True
End With
Range("A2") = "- unposted checks"
Range("D2") = "- unposted debits"
Range("A3") = "+ unposted deposits"
Range("D3") = "+ unposted credits"
Range("A4") = "New Balance"
Range("D4") = "New Balance"
Range("A7") = "Outstanding Checks"
Range("D7") = "Unposted Checks"
Range("A8") = "and withdrawals not"
Range("D8") = "and withdrawals not"
Range("A9") = "on statement"
Range("D9") = "in register"
Range("A7:A9").Font.Bold = True
Range("D7:D9").Font.Bold = True
Range("A14") = "Total"
Range("D14") = "Total"
Range("A16") = "Deposits or Credits"
Range("D16") = "Deposits or Credits"
Range("A17") = "not on statement"
Range("D17") = "not in register"
Range("A16:A17").Font.Bold = True
Range("D16:D17").Font.Bold = True
Range("A19") = "Total"
Range("D19") = "Total"
Range("H3") = "Error Amount"
With Range("H10")
.HorizontalAlignment = xlCenter
.Value = "DO NOT USE CURRENCY SYMBOL IN INPUT BOX"
.Font.ColorIndex = 5
.Font.Size = 10
End With
With Range("C24")
With .Font
.Size = 20
.Bold = True
.ColorIndex = 5
End With
.HorizontalAlignment = xlCenter
.Value = "DO NOT MAKE MANUAL ENTRIES ON FORM"
End With
End Sub

Sub mkFormBBlines()
Range("A1:E22").BorderAround Weight:=xlMedium
With Range("A1:A22").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Range("D1:D22").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Range("C1:C22").Interior.ColorIndex = 16
With Range("B1, E1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Range("B2:B4, E2:E4").Borders(xlInsideHorizontal)
.LineStyle = xlDouble
End With
With Range("B7:B14, E7:E14").Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Range("B13, E13").Borders(xlEdgeBottom)
.LineStyle = xlDouble
End With
With Range("B16:B18, E16:E18").Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Range("B18, E18").Borders(xlEdgeBottom)
.LineStyle = xlDouble
End With
Range("H3").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
Range("H4").BorderAround LineStyle:=xlDash, Weight:=xlMedium
Range("F1:T45, A23:E45").Interior.ColorIndex = 4
Range("H3:H4").Interior.ColorIndex = xlColorIndexNone

End Sub

Sub mkFormBBfmla()
Range("B2").Formula = "=B14"
Range("E2").Formula = "=E14"
Range("B3").Formula = "=B19"
Range("E3").Formula = "=E19"
Range("B4").Formula = "=IF(B1 > 0,SUM(B1:B3),"""")"
Range("E4").Formula = "=IF(E1 > 0,SUM(E1:E3),"""")"
Range("B14").Formula = "=IF(B7>0,SUM(B7:B13)*(-1),"""")"
Range("E14").Formula = "=IF(E7 > 0,SUM(E7:E13)*(-1),"""")"
Range("B19").Formula = "=IF(B16>0,SUM(B16:B18),"""")"
Range("E19").Formula = "=IF(E16 > 0,SUM(E16:E18),"""")"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I add a break after each subtotal? 1
can you help me set up a macro 0
simple macro to compare lists 1
Help with macro comparing data in worksheets 4
Macro help please! 7
how can i create a macro that insert 2 rows 1
Help needed with Macro designed to compare data... 2
Referencing a Row value from within a VLOOKUP 5

Top