See also use of OFFSET in formulas which simplifies the insertion, and deletion of rows with the Insert Rows And Fill Formulas macro and allows you to sort the rows; whereas, sorting rows containing simple addition and subtraction of relative addresses would make a mess. Select End Sub Note that there is an optional parameter that can be used from another macro. Clear Contents on error goto 0 End Sub The following Worksheet Double Click Event Macro will copy formulas down from the last row, and increment the value in Column A of the last row by 7 days for the new row then place the cursor in the first empty cell of the new row.
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.
", Title:="Add Rows", _ Default:=1, Type:=1) 'Default for 1 row, type 1 is number If v Rows = False Then Exit Sub End If 'if you just want to add cells and not entire rows 'then delete ".
By referencing cells in the current row any insertion or deletion of lines will cause formulas to be updated. 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). Dim x As Long Cancel = True 'turn off Edit, mode applicable to "Edit directly in a cell" x = Active Sheet. 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. The primary macro Insert Rows And Fill Formulas is installed on a toolbar button.
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. Just make sure that your number is not larger than your criteria. Column)) = "" Then Msg Box "skipping due to formula in column C and nothing in Column A --testing" Exit Sub End If Application. Mc Ritchie, 2007-05-11, not posted Cancel = True Target. The trick is to look for a large number that can’t exist.If you maintain a checkbook in Excel you can add up deposits for a deposit slip by selecting the range and looking at the SUM= on the status bar. Clear Contents End If End Sub Event macros are installed differently that standard macros. 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. [offset], [insrtrow] [dclick], [copyformulas], [Insert Blank Rows], [moreresize], [Change Event], [extended], [Alt Blanks], [Col Achg], [before_a], [reconcile], [Manual Insert], [Delete K], [Delete R], [addendum], [sign_onright], [fixrightminus], [clear Cols], [extra Totals], [blankrows], [multilabel], [#VALUE!