jump to new cell

G

Guest

I have a worksheet that is a few columns that are added
into totals at the bottom. There are 100 rows. Is there
a way to make the active cell move to the top of the next
column when they hit enter on the 100th cell.
For example: when they enter a number in cell a100 it will
automatically jump to cell b1 for the next entry instead
of going down to a101?
Thanks,
 
H

Harald Staff

Hi

If you select a range of cells (like A1:B100) then Excel will move between
these cells only.

HTH. Best wishes Harald
 
J

jeff

HI,

Try this - you put the 101st value in cell a101; it
'wraps' on each 100th row.
jeff


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
Dim lastcol As Integer
If Intersect(Target, Me.Range("a101:a101")) Is Nothing
Then Exit Sub
lastcol = ActiveSheet.Cells("1", Columns.Count).End
(xlToLeft).Column
lastrow = ActiveSheet.Cells(Rows.Count, lastcol).End
(xlUp).Row
Application.EnableEvents = False
If lastrow = 5 And lastcol > 1 Then
lastcol = lastcol + 1
lastrow = 1
Else
lastrow = lastrow + 1
End If
Cells(lastrow, lastcol) = Target.Value
Target = ""
Application.EnableEvents = True
Range("A100").Select
End Sub
 
Top