Basic newbie question

[John]

I have a setup of a manually entered grid of data with 40 rows and 12
columns. Each week I need to update Row1 Col1, move to Col 10 and enter
some data, then on to Row2 Col1 ... Col10, etc...

I would like to code the following...
After data is entered in Row1 Col1, move to Row1 Col10, after data is
entered in Row1 Col10, move to Row2 Col1, etc..

What code would do that for me? I think if I could see the code to do one
iteration, I can figure out how to loop it for 40 rows.

Thanks in advance,
John

 

[Jason Lepack]

As long as you are actually changing something in each cell then you
could place this macro into your Worksheet under "Tools->Macros->VB
Editor"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then
Target.Offset(1, 1).Select
ElseIf Target.Column = 11 Then
Target.Offset(0, -1).Select
End If
End Sub

If it's a systematic change that is doen every day, you could automate
it.

Cheers,
Jason Lepack

 

[John]

Jason,
OK... that's just what I need. I have two other tables of data on that
worksheet that require different "shifting" schemes, but now I know I can
ask where the cursor is and move accordingly. Just what I neeed... a
start...
I'm thinking I may be able to interrogate what "Range" I'm in as well as
what column.

Thanks Jason,
John

 

[Jason Lepack]

You can use Target.Row in the same fashion.

Target.Address will give you the address of the cell in the form "$A$1"

Cheers,
Jason Lepack

 

[John]

Thanks Jason,
I'm digging in there as we speak...
John