moving cell after calculation

[greg7468]

Hello all,
I am compiling a hours worked sheet. I have assigned a button whic
adds 7 to the active cell, what do I have to do to get the active cel
to move one cell down in the same column so I do not have to manuall
move the active cell down.
thanks for your help

 

[Nikos Yannacopoulos]

Greg,

Add the following line at the end of your macro code:

ActiveCell.Offset(1,0).Select

HTH,
Nikos

 

[greg7468]

Nikos,

Thank you.

Greg

 

[David McRitchie]

Hi Greg,
activecell.offset(1,0) = activecell.value + 7

But if the button is adding another row to the end of your spreadsheet
in column A, you might want something more along the lines of

The following 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.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lastrow As Long 'D.McRitchie, 2004-06-10, newusers
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents
Cells(lastrow + 1, 1) = Cells(lastrow, 1) + 7
Rows(lastrow + 1).SpecialCells(xlCellTypeBlanks).Item(1).Activate
End Sub

 

[David McRitchie]

Greg,
Actually I gave you an Event macro without telling you how to
install because I was thinking you would just copy the code
inside to your macro code for the button, if it was of interest.
But if you want to test it first as a double-click event macro.

Right click on the sheet tab, view code, place code inside.

More information on Inserting Rows and maintaining formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
More information on Event Macros
http://www.mvps.org/dmcritchie/excel/event.htm