What your "new" code is doing is very similar to what I gave you.
the primary difference is that mine was using absolute column value,
for the destination (goto next row, first column)
theirs is calculating the number of rows and columns to move relative
to the current position.
Offset (1) row positive and move five columns to the left
(current column is 6, -1 * 6 = move left 6, then + 1)
I suspect the reason mine doesn't work for you is that you said that you
were entering FIVE readings. I "assumed" that meant that the
last reading was in column "E", or 5 and tested for that.
theirs says 6...
Another difference is that I was using a CHANGE event.
meaning no matter how many movements, clicks, arrows, whatever you do
in the sheet, the macro does not run.
It only runs if you CHANGE something. Then it tests to see if the cell
changed is in the specified column.
THEIRS is using the SelectionChange event, which executes every time
you use the mouse to click in a cell, or use the arrow keys.
That means that since your scanner automatically "selects" the cell in column 6,
then it fires off and moves the cursor.
Mine will only run if you make an entry in column 5.
If you want, I can walk you through setting a debug "breakpoint" that
will allow you to halt the macro execution and let you step through it one line
at a time...
Paul
________________________________
From: lucahk <luc...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Wednesday, April 22, 2009 8:06:49 AM
Subject: $$Excel-Macros$$ Re: Go to new row after column X
Dear Paul, thank you very much for your reply and your help ! :)
I am also happy you think what I am doing is cool, eheh !
I have several excel files, one for each product I have to check.
Each file I only use 1 sheet.
Your assumption that I know nothing about VBA is totally correct, and
I thank you for the explanation you gave in your email.
I am using Excel 2007, and the developer menu is enabled ... this is
nearly the only thing I can do :p
I have tried to use your code, but I don't know why it seems it does
not work, it will not change to a new row ....
In the meantime I was asking some other people on the Microsoft forum
in Italian, and someone suggested me this code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const col As Long = 6
If Target.Column = col Then _
Target.Offset(1, (-1 * col) + 1).Select
End Sub
with this code it can go down when it reaches the column, but then I
don't know why when I move right the first time in the new row, it
will jump on row up again.
I will keep on trying ! :)
Thanks very much again for your help !
Luca.
On Apr 22, 1:48 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> Interesting... really cool too..
> Is this Excel file one that you use all the time?
> or is it new each time?
> all on the same sheet? or new sheets?
>
> How much do you know about programming in Excel VBA?
> I'm rather than wait for 3-4 emails, I'm going to "assume" that
> you don't know VBA... you'll have to learn a little along the way.
> I'm also going to assume you're using Excel97/2003 (pre 2007)
> or if you are using 2007, you've got the Developer menu enabled.
>
> My first approach would be to create a "change event" in Excel
>
> Let's say that your fifth measurement is placed in column "E"
>
> In Excel, right-click on the sheet name and select "View Code"
> In the upper section (above the text area) use the pull-down
> for the left-hand box to select "Worksheet".
> The default "event" is SelectionChange, and the macro is created.
> In the right-hand box, use the pull-down to select the "Change" event.
>
> It will create the sub:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> End Sub
> (you can delete the one:
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> End Sub
> )
>
> this macro waits for any changes TO THIS SHEET.
> the cells changed are passed to the macro as the variable "Target"
>
> Now, copy/paste this line between the Sub/End Sub lines:
> If (Target.Column = 5) Then Cells(Target.Row + 1, 1).Select
>
> You now have:
> Private Sub Worksheet_Change(ByVal Target As Range)
> If (Target.Column = 5) Then Cells(Target.Row + 1, 1).Select
> End Sub
>
> this tests to see if the cell changed is in column 5 ("E")
> If it is, then it selects the cell in the next row (target.row+1)
> and the first column (,1)
>
> If you want this to work for ALL sheets in a workbook,
> we can place a similar macro in another VBA module location.
> the name changes, and there is some other changes that you'll need.
>
> let me know what you think, what your situation is, and if you need more help.
>
> Paul
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
