Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set up a macro to help me to do bank reconciliations?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set up a macro to help me to do bank reconciliations?
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(B70,SUM(B7:B13)*(-1),"""")" Range("E14").Formula = "=IF(E7 0,SUM(E7:E13)*(-1),"""")" Range("B19").Formula = "=IF(B160,SUM(B16:B18),"""")" Range("E19").Formula = "=IF(E16 0,SUM(E16:E18),"""")" End Sub "Jaime" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need a bank rec template | New Users to Excel | |||
How do I set up a macro to help me to do bank reconciliations? | Excel Programming | |||
Macro for Bank Reconciliation | Excel Discussion (Misc queries) | |||
Bank Links | Excel Discussion (Misc queries) | |||
Bank Reconciliation | Excel Discussion (Misc queries) |