How to automatically move to a new cell...

  • Thread starter Thread starter sortatech
  • Start date Start date
S

sortatech

....After data is entered.

Hi, This seems one of the most helpful and friendly groups available. If
anyone can help, I'll be grateful.

Current method:
We have a device that measures color density of printed patterns.
It automatically exports its data (with a return) to an Excel cell and moves
to the cell below it.
The sheet is set up so that data is entered in five cells in a column and
the 6th cell averages the five entries.
The user must then manually arrow up to the top of the next column.
Arrowing right and up four may not sound like a big deal but the user may
sample a hundred spots at a sitting so the flow is constantly interrupted as
he/she goes back and forth between the device and Excel.
----------------
What we want to do:
After the fifth entry is made, automatically go to the top of the next
column.
Example:
Data is entered in C3 through C7
It is AVERAGED in C8
Then automatically moves to the next cell for data entry (which would be D3)
It should do this forever or until the user quits or dies of boredom.


Thanks

Dan
The irony that my greatest intolerance is for intolerance is not lost upon
me.
 
You can use an Event macro -- applies only to the sheet it is on.
http://www.mvps.org/dmcritchie/excel/event.htm


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Then Exit Sub
If Target.Row < 8 Then Exit Sub
On Error Resume Next 'MUST reenable events...
' Application.EnableEvents = False
If Target.Row > 8 Then
Target.Offset(-Target.Row + 1, 1).Activate
GoTo done
End If
If Target.Row = 8 Then
Target.Formula = "=SUM(" & Target.Offset(-5, 0).Address(0, 0) _
& ":" & Target.Offset(-1, 0).Address(0, 0) & ")/5"
Target.Offset(-6, 0) = Now
End If
Target.Offset(-Target.Row + 1, 1).Activate
done:
Application.EnableEvents = True
End Sub


If the Event macro terminates you may have to run
this macro .

Sub Fix_Things()
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change macro
End Sub
 
You can try this as a quick and simple solution to prevent your users from
dieing of boredom.

Click in C3 and drag to (for example) J7.
You now have a range selected, with the focus in C3.
While this range is still selected, start typing numbers, and see what
happens after you hit <Enter> in cell C7.
Amazing, isn't it ?

This all takes only a second, and you can make it as large or small as you
wish, and you can throw it away and start over as you please.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

....After data is entered.

Hi, This seems one of the most helpful and friendly groups available. If
anyone can help, I'll be grateful.

Current method:
We have a device that measures color density of printed patterns.
It automatically exports its data (with a return) to an Excel cell and moves
to the cell below it.
The sheet is set up so that data is entered in five cells in a column and
the 6th cell averages the five entries.
The user must then manually arrow up to the top of the next column.
Arrowing right and up four may not sound like a big deal but the user may
sample a hundred spots at a sitting so the flow is constantly interrupted as
he/she goes back and forth between the device and Excel.
----------------
What we want to do:
After the fifth entry is made, automatically go to the top of the next
column.
Example:
Data is entered in C3 through C7
It is AVERAGED in C8
Then automatically moves to the next cell for data entry (which would be D3)
It should do this forever or until the user quits or dies of boredom.


Thanks

Dan
The irony that my greatest intolerance is for intolerance is not lost upon
me.
 
Hi, Sorry it took me so long to get back to. Thank you very much for not
only your assistance but also the incredibly fast response time.

For now I'm using the "select a range" idea to get us going. The other
solution is what we'll end up going with long term but it's a bit
overwhelming as my Excel skills are very limited.

Thanks Again,

Dan

PS I can't believe I forgot to disguise my email address, Since I posted
I've received two viruses and an offer from one Dr. Maksela to give me 10%
of $35,000,000! Who needs a job when you're expecting 3.5 million to come
through any minute? Oh well, that's what disposable addresses are for.
 
Back
Top