macro problem

  • Thread starter Thread starter wooo
  • Start date Start date
W

wooo

Hi all,

In my routine job, I have to input data in excel.

I want that after I input data in Range "A1", then press
Enter button, it will move to right to let me input other
data Range" B1", then I press Enter button again, it can
move to Range"C1" to let me input data. And then, When
finish input data in these three cells, it can move to
next row to let me in next three cells again.........


I tried to use macro to record these steps, but when the
macro is running, it will not let me input data, it just
replay what I input before. How can I create macro to let
me input data and it will move to next cell automatically.
 
right click sheet tab>view code>insert this>modify to suit. As I use it
ie:if cursor is on row 6 or below and target column is 8 because I entered
data in col 3,4,5,6,7 and moved the cursor using the right arrow key.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row > 5 And Target.Column = 8 Then ActiveCell.Offset(1, -6).Select
End Sub
 
You could accomplish what you want without a macro. I am using Exce
2000 and I would do the following:

First:
Select Tools/Options/Edit Tab/put a check mark in box "Move selectio
after Enter" and select "right" in the Direction drop-down list

Second:
Select the range of cells where you input your data and then selec
Format/Cells/Protection Tab/ remove the check mark from the "Locked
box

Now when you start inputting your data the cell to the right wil
become active every time you hit Enter but will drop down to first cel
of next row when you hit Enter from the last cell of each row
 
wooo

Without a macro, there are a few methods to do this.

To TAB to desired cells.......

1. Unlock the cells you want to TAB to then protect the worksheet.

If your unlocked cells are in a left to right, top to bottom series, the TAB
key will move you through them as long as Sheet Protection is enabled.

2. If not in this configuration.....you can do it with a Named Range and no
sheet protection.

Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.

Select the Second cell(B2) you want in the range then CRTL + click your way
through the range in the order you wish, ending with the First cell(A1). Name
this range under Insert>Name>Define>OK.

Now click on NameBox(top left corner above row 1 and col A), select the range
name to highlight the range. With these cells selected, you can input data
and Tab or Enter your way through the range in the order you selected.

Note: there is a limit of about 25 - 30 cells to a range using this method due
to a 255 character limit in a named range. Longer sheet names will reduce the
number of cells considerably.

If more needed, you can enter them manually in thr "refers to" box.

From Debra Dalgleish.....
The limit is 255 characters in the Name definition. For example, I can
define a range of 46 non-contiguous cells, with the following string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4,$B$6,$D$6,$F$6,$H$6,
$J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$12,
$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$B$16,$D$16,$F$16,$H$16,
$J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3
Click to expand...

Third method.......

Assume start at A1. Hit TAB key to B1, C1, D1, E1 then hit <ENTER> key to go
to A2.

Done this way, the <ENTER> key takes you back to the column you started in and
one row down.

Gord Dibben Excel MVP
 
Back
Top