Normally the dates in Column B are entered using the shortcut key for the current date [Ctrl semi-colon]. It would be best to keep the macro as is for general use, but when calling from a macro to have the calling macro include the number of rows to be inserted and no Msg Box. The last row is determined by having content in Column A. When using a Debit card enter a letter instead of the date into the reconciled column. Select the row above where you want to insert the rows, this is also the row whose formulas will be copied down. Private Sub Worksheet_Before Double Click(By Val Target As Range, Cancel As Boolean) Dim lastrow As Long 'D. Checking will be from current row and from next row in a separate macro that calls Insert Rows And Fill Formulas. Has Formula Private Sub Worksheet_Before Double Click(By Val Target As Range, Cancel As Boolean) '-- Code example to prevent reinserting if the current row '-- or next row row has Column A cel empty (testing with TRIM). Count 'may alleviate some lastcell problems Target. Enable Events = False 'should be part of Change macro Target. Enable Events = True 'should be part of Change macro End Sub Another Example User, 2004-05-10, did not want to reinsert if already done. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.The propagation of data would be misleading so I want it to be blank so I have to enter it myself. Resize(2), xl Fill Default on error resume next Rows(lastrow 1). Clear Contents on error goto 0 Cells(lastrow 1, 1) = Cells(lastrow, 1) 7 Rows(lastrow 1). Private Sub Worksheet_Before Double Click(By Val Target As Range, Cancel As Boolean) 'David Mc Ritchie, 2007-09-07 on double-click '-- will copy more often than Extend Formulas and Format (tools option) Cancel = True Target.
I then found and included a macro close to what I wanted and described again exactly what I really needed.
The lazy way of keeping your bank balances is to use Quicken (or MS Money) and download records from you financial institutions. Each of the Event macros below are Worksheet Event Macros and can be installed by right clicking on the worksheet tab then View Code, then insert the event macro. Private Sub Worksheet_Before Double Click(By Val Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick Target.
Unfortunately you would not see both dates -- the date on your check and the date it cleared.
In order to refer to cells elsewhere but to name only cell addresses in the current row, OFFSET will be used as follows: Replication is made easier by grabbing and dragging the fill handles and with the use of shortcuts Ctrl D (Fill Down), and Ctrl R (Fill Right).
OFFSET makes Replication/Deletion of Rows tie in better with the other rows, and of course, makes possible the Insert Rows And Fill Formulas macro described in the next topic.
Both macros are installed on a module sheet in my The code below has extra blank lines to help identify long lines it they get wrapped when you copy them.