go to command

[Dennis]

We are using Excel 2000 at work for a worksheet. We use a barcode scanner to
insert numbers into cells which inserts number then goes to cell below. When
we get to bottom of first column we have to manually move up to begining cell
of next column. Is there a command that I can put at the bottom of the first
column that will move the cursor up to the the top cell of the next.

 

[Roger Govier]

Hi Dennis

You need some event code to do this.
The following code assumes you are going down to row 20, and, after entering
data there you want to jump to row 2 of the next column.

Adjust the numbers to suit your requirement.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxrow As Long, firstrow As Long
maxrow = 20
firstrow = 2
If Target.Row = maxrow Then
Cells(firstrow, Target.Column + 1).Activate
End If
End Sub

To Use
Copy code
Right click on Sheet tab>View code
Paste code into white pane that appears
Alt+F11 to return to Excel

--
Regards
Roger Govier

We are using Excel 2000 at work for a worksheet. We use a barcode scanner
to
insert numbers into cells which inserts number then goes to cell below.
When
we get to bottom of first column we have to manually move up to begining
cell
of next column. Is there a command that I can put at the bottom of the
first
column that will move the cursor up to the the top cell of the next.

__________ Information from ESET Smart Security, version of virus
signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Gord Dibben]

Where is bottom of first column?

A65536 or some arbitrary row reference?


Gord Dibben MS Excel MVP

 

[Dennis]

the worksheet has protected cells at beginning of sheet and all around the
cells scanned into. What is needed is after number is scanned to c24 cell the
cursor then automatically moves to C25. This is where I need a command to
take the cursor and move it to F8 cell so more scanning can be done. Moving
manually works but if it can automatically move the cursor there, the techs
can scan without stopping. This command will work for this worksheet?

 

[L. Howard Kittle]

Does this mod of Roger's code get you there?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxrow As Long, firstrow As Long
maxrow = 25
'firstrow = 2
If Target.Row = maxrow Then
Target.Offset(-17, 5).Activate
'Cells(firstrow, Target.Column + 4).Activate
End If
End Sub

HTH
Regrds,
Howard

 

[Dennis]

tried it and don't work for me. Will try it at work and see if I can get it
to work. Probably something I am doing wrong. Not very good with this sort of
thing.

 

[L. Howard Kittle]

This will take you from C25 to F8. Will only work for column C

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxrow As Long, firstrow As Long
If Target.Column <> 3 Then Exit Sub
maxrow = 25
firstrow = 8
If Target.Row = maxrow Then
Cells(firstrow, Target.Column + 3).Activate
End If
End Sub

HTH
Regards,
Howard