Scripts to stop and start on another row

[kite55surfer]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

Hi all,

I have this macro on 2004 to start again 2 rows down.
===================================

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 14 Then 'Column N
Cells(Target.Row + 2, "A").Select
End If
Application.EnableEvents = True

End Sub
========================================
I am an ex-windows and would like to know if there is a script to accomplish this?

Also : where would I put the script?

Thanks in advance for any help!

Emilio

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

Hi all,

I have this macro on 2004 to start again 2 rows down.
===================================

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 14 Then 'Column N
Cells(Target.Row + 2, "A").Select
End If
Application.EnableEvents = True

End Sub
========================================
I am an ex-windows and would like to know if there is a script to accomplish
this?

Also : where would I put the script?

Thanks in advance for any help!

Emilio

This is an event driven macro which is difficult (if not impossible) to
implement in applescript. In VBA, this macro runs when ever the selected
cell changes. Even clicking on a cell when data is not entered. What are
you trying to accomplish?

If you simply want the active cell to change to 2 rows down in column A when
data is entered in column N, you can accomplish this with an XLM macro. Add
a macro sheet by pressing Ctrl-F11. Then in A1 & A2 enter:
=on.entry(,"r1c2")
=return()
Beginning in cell B1 of the macro sheet, enter the following:

=COLUMN(SELECTION())
=ROW(SELECTION())
=IF(B1=14,,RETURN())
=SELECT("r"&B2+1&"c1")
=RETURN()

Then, with A1 selected on the macro sheet, go to tools, macro Run.
Now, when you switch back to the worksheet, it will automatically select A1
2 rows down when data is entered into a cell in column N.

To get this to start automatically when the worksheet is opened, define a
name "Autpen" and have it refer to Macro1!a1.

 

[kite55surfer]

On 4/1/08 8:53 PM, in article (e-mail address removed)9absDaxw,

:

> Version: 2008
> Operating System: Mac OS X 10.5 (Leopard)
> Processor: intel
>
> Hi all,
>
> I have this macro on 2004 to start again 2 rows down.
> ===================================
>
> Option Explicit
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> Application.EnableEvents = False
> If Target.Column = 14 Then 'Column N
> Cells(Target.Row + 2, "A").Select
> End If
> Application.EnableEvents = True
>
> End Sub
> ========================================
> I am an ex-windows and would like to know if there is a script to accomplish
> this?
>
> Also : where would I put the script?
>
> Thanks in advance for any help!
>
> Emilio
This is an event driven macro which is difficult (if not impossible) to
implement in applescript. In VBA, this macro runs when ever the selected
cell changes. Even clicking on a cell when data is not entered. What are
you trying to accomplish?

If you simply want the active cell to change to 2 rows down in column A when
data is entered in column N, you can accomplish this with an XLM macro. Add
a macro sheet by pressing Ctrl-F11. Then in A1 & A2 enter:
=on.entry(,"r1c2")
=return()
Beginning in cell B1 of the macro sheet, enter the following:

=COLUMN(SELECTION())
=ROW(SELECTION())
=IF(B1=14,,RETURN())
=SELECT("r"&B2+1&"c1")
=RETURN()

Then, with A1 selected on the macro sheet, go to tools, macro Run.
Now, when you switch back to the worksheet, it will automatically select A1
2 rows down when data is entered into a cell in column N.

To get this to start automatically when the worksheet is opened, define a
name "Autpen" and have it refer to Macro1!a1.

Thanks for your help
I followed the instructions but I can't get it to work.
What I want is as I enter data when I reach column 14 to jump down 2 rows and start again on 1st column.

Thanks again

 

[Bob Greenblatt]

Thanks for your help
I followed the instructions but I can't get it to work.
What I want is as I enter data when I reach column 14 to jump down 2 rows and
start again on 1st column.

Thanks again

Did you select A1 on the macro sheet and then go to tools macro run? Then
switch back to the works sheet and it should work fine.

 

[kite55surfer]

On 4/2/08 9:37 AM, in article (e-mail address removed)9absDaxw,

:

>> On 4/1/08 8:53 PM, in article (e-mail address removed)9absDaxw,
>> "(e-mail address removed)" wrote:
>>
>> > Version: 2008
>> > Operating System: Mac OS X 10.5 (Leopard)
>> > Processor: intel
>> >
>> > Hi all,
>> >
>> > I have this macro on 2004 to start again 2 rows down.
>> > ===================================
>> >
>> > Option Explicit
>> >
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> >
>> > Application.EnableEvents = False
>> > If Target.Column = 14 Then 'Column N
>> > Cells(Target.Row + 2, "A").Select
>> > End If
>> > Application.EnableEvents = True
>> >
>> > End Sub
>> > ========================================
>> > I am an ex-windows and would like to know if there is a script to
>> accomplish
>> > this?
>> >
>> > Also : where would I put the script?
>> >
>> > Thanks in advance for any help!
>> >
>> > Emilio
>> This is an event driven macro which is difficult (if not impossible) to
>> implement in applescript. In VBA, this macro runs when ever the selected
>> cell changes. Even clicking on a cell when data is not entered. What are
>> you trying to accomplish?
>>
>> If you simply want the active cell to change to 2 rows down in column A when
>> data is entered in column N, you can accomplish this with an XLM macro. Add
>> a macro sheet by pressing Ctrl-F11. Then in A1 & A2 enter:
>> =on.entry(,"r1c2")
>> =return()
>> Beginning in cell B1 of the macro sheet, enter the following:
>>
>> =COLUMN(SELECTION())
>> =ROW(SELECTION())
>> =IF(B1=14,,RETURN())
>> =SELECT("r"&B2+1&"c1")
>> =RETURN()
>>
>> Then, with A1 selected on the macro sheet, go to tools, macro Run.
>> Now, when you switch back to the worksheet, it will automatically select A1
>> 2 rows down when data is entered into a cell in column N.
>>
>> To get this to start automatically when the worksheet is opened, define a
>> name "Autpen" and have it refer to Macro1!a1.
>>
>>
>>
>>
>> --
>> Bob Greenblatt [MVP], Macintosh
>> bobgreenblattATmsnDOTcom
>>
>>
> Thanks for your help
> I followed the instructions but I can't get it to work.
> What I want is as I enter data when I reach column 14 to jump down 2 rows and
> start again on 1st column.
>
> Thanks again
Did you select A1 on the macro sheet and then go to tools macro run? Then
switch back to the works sheet and it should work fine.

I must be doing something wrong

I have a sheet called Macro1 with

A1 =ON.ENTRY(,"r1c2")
A2 =RETURN()
B1 =COLUMN(SELECTION())
B2 =ROW(SELECTION())
B3 =IF(B1=14,,RETURN())
B4 =SELECT("r"&B2+1&"c1")
B5 =RETURN()

Then select A1 (in Macro1 sheet) and tools macro run, then I go to the original sheet and start in A1 trying to enter data but nothing happens when I get to N1 (column 14).

Sorry to be a pest.

Thanks again,
Emilio

 

[Bob Greenblatt]

I must be doing something wrong

I have a sheet called Macro1 with

A1 =ON.ENTRY(,"r1c2")
A2 =RETURN()
B1 =COLUMN(SELECTION())
B2 =ROW(SELECTION())
B3 =IF(B1=14,,RETURN())
B4 =SELECT("r"&B2+1&"c1")
B5 =RETURN()

Then select A1 (in Macro1 sheet) and tools macro run, then I go to the
original sheet and start in A1 trying to enter data but nothing happens when I
get to N1 (column 14).

Sorry to be a pest.

Thanks again,
Emilio

I'm sorry it is not working for you. Your code is correct. I just tried
again, by copying your code to a new workbook and it works fine for me. Did
you save and reopen the workbook? Is the macro sheet present when you reopen
the workbook? If so, did you rerun the macro in A1? Are you pressing the
return key after entering data in column N?

If the macro sheet is missing when you reopen the workbook. Change the Excel
Preferences-Security to "warn before opening a file containing macros". If
this preference is not checked, excel will remove the macro sheet.

 

[kite55surfer]

>
> I must be doing something wrong
>
> I have a sheet called Macro1 with
>
> A1 =ON.ENTRY(,"r1c2")
> A2 =RETURN()
> B1 =COLUMN(SELECTION())
> B2 =ROW(SELECTION())
> B3 =IF(B1=14,,RETURN())
> B4 =SELECT("r"&B2+1&"c1")
> B5 =RETURN()
>
> Then select A1 (in Macro1 sheet) and tools macro run, then I go to the
> original sheet and start in A1 trying to enter data but nothing happens when I
> get to N1 (column 14).
>
> Sorry to be a pest.
>
> Thanks again,
> Emilio
I'm sorry it is not working for you. Your code is correct. I just tried
again, by copying your code to a new workbook and it works fine for me. Did
you save and reopen the workbook? Is the macro sheet present when you reopen
the workbook? If so, did you rerun the macro in A1? Are you pressing the
return key after entering data in column N?

If the macro sheet is missing when you reopen the workbook. Change the Excel
Preferences-Security to "warn before opening a file containing macros". If
this preference is not checked, excel will remove the macro sheet.

Yes to all of the above.
I am going to keep trying, at least I know code is OK

Thanks a million,
Emilio