Go Immediately To A Particular Cell After Inputting Data In Another Cell And Hitting Enter

I'm inputting data in E3 and after I hit enter I want B2 to be
selected.  But only for E3 and only in one worksheet.  And possibly a
further step...  Sometimes the formula in B2 has picked up data from
another place depending on what value was entered in E3.  If it's done
this then I won't need to go to B2, I'd want to go to A8 after E3
instead of B2.
0
robzrob
2/20/2010 10:35:19 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
668 Views

Similar Articles

[PageSpeed] 42

Put this in the code module of the worksheet that contains the ranges you 
are working with.  Right click the sheet name tab, select View Code from the 
pop up menu and paste this into the code window that appears on screen.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target = Range("E3") Then
      If Range("B2") > 0 Then
         Range("A8").Select
      Else
         Range("B2").Select
      End If
   End If
End Sub




"robzrob" <robzrob@hotmail.com> wrote in message 
news:5f926433-025c-4103-973d-38aa7958a16b@b7g2000yqd.googlegroups.com...
> I'm inputting data in E3 and after I hit enter I want B2 to be
> selected.  But only for E3 and only in one worksheet.  And possibly a
> further step...  Sometimes the formula in B2 has picked up data from
> another place depending on what value was entered in E3.  If it's done
> this then I won't need to go to B2, I'd want to go to A8 after E3
> instead of B2. 


0
JLGWhiz
2/21/2010 12:12:38 AM
Need to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target = Range("E3") Then
      If Range("B2").Value > "" Then
         Range("A8").Select
      Else
         Range("B2").Select
      End If
   End If


It sees the formula as greater than zero, but not the empty string.


"robzrob" <robzrob@hotmail.com> wrote in message 
news:5f926433-025c-4103-973d-38aa7958a16b@b7g2000yqd.googlegroups.com...
> I'm inputting data in E3 and after I hit enter I want B2 to be
> selected.  But only for E3 and only in one worksheet.  And possibly a
> further step...  Sometimes the formula in B2 has picked up data from
> another place depending on what value was entered in E3.  If it's done
> this then I won't need to go to B2, I'd want to go to A8 after E3
> instead of B2. 


0
JLGWhiz
2/21/2010 12:25:08 AM
On Feb 21, 12:25=A0am, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
> Need to modify the code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 =A0If Target =3D Range("E3") Then
> =A0 =A0 =A0 If Range("B2").Value > "" Then
> =A0 =A0 =A0 =A0 =A0Range("A8").Select
> =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0Range("B2").Select
> =A0 =A0 =A0 End If
> =A0 =A0End If
>
> It sees the formula as greater than zero, but not the empty string.
>
> "robzrob" <robz...@hotmail.com> wrote in message
>
> news:5f926433-025c-4103-973d-38aa7958a16b@b7g2000yqd.googlegroups.com...
>
>
>
> > I'm inputting data in E3 and after I hit enter I want B2 to be
> > selected. =A0But only for E3 and only in one worksheet. =A0And possibly=
 a
> > further step... =A0Sometimes the formula in B2 has picked up data from
> > another place depending on what value was entered in E3. =A0If it's don=
e
> > this then I won't need to go to B2, I'd want to go to A8 after E3
> > instead of B2.- Hide quoted text -
>
> - Show quoted text -

Perfect.  Thank you.
0
robzrob
2/21/2010 12:35:43 AM
On Feb 21, 12:25=A0am, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
> Need to modify the code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 =A0If Target =3D Range("E3") Then
> =A0 =A0 =A0 If Range("B2").Value > "" Then
> =A0 =A0 =A0 =A0 =A0Range("A8").Select
> =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0Range("B2").Select
> =A0 =A0 =A0 End If
> =A0 =A0End If
>
> It sees the formula as greater than zero, but not the empty string.
>
> "robzrob" <robz...@hotmail.com> wrote in message
>
> news:5f926433-025c-4103-973d-38aa7958a16b@b7g2000yqd.googlegroups.com...
>
>
>
> > I'm inputting data in E3 and after I hit enter I want B2 to be
> > selected. =A0But only for E3 and only in one worksheet. =A0And possibly=
 a
> > further step... =A0Sometimes the formula in B2 has picked up data from
> > another place depending on what value was entered in E3. =A0If it's don=
e
> > this then I won't need to go to B2, I'd want to go to A8 after E3
> > instead of B2.- Hide quoted text -
>
> - Show quoted text -

Spoke too soon!  I have another macro in this workbook which, after
the workbook has been used, clears all the values from all the cells -
including E3.  Now it won't work because when it gets to this line

    Range("E3:E5").Select
    Selection.ClearContents

in my code, 'your' code is activated and it all come to a standstill.
0
robzrob
2/21/2010 12:45:24 AM
Try it with this modification:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target = Range("E3") And Target <> "" Then
      If Range("B2").Value > "" Then
         Range("A8").Select
      Else
         Range("B2").Select
      End If
   End If




"JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
news:%23Kv$dyosKHA.5356@TK2MSFTNGP02.phx.gbl...
> Need to modify the code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>   If Target = Range("E3") Then
>      If Range("B2").Value > "" Then
>         Range("A8").Select
>      Else
>         Range("B2").Select
>      End If
>   End If
>
>
> It sees the formula as greater than zero, but not the empty string.
>
>
> "robzrob" <robzrob@hotmail.com> wrote in message 
> news:5f926433-025c-4103-973d-38aa7958a16b@b7g2000yqd.googlegroups.com...
>> I'm inputting data in E3 and after I hit enter I want B2 to be
>> selected.  But only for E3 and only in one worksheet.  And possibly a
>> further step...  Sometimes the formula in B2 has picked up data from
>> another place depending on what value was entered in E3.  If it's done
>> this then I won't need to go to B2, I'd want to go to A8 after E3
>> instead of B2.
>
> 


0
JLGWhiz
2/21/2010 1:15:54 AM
On Feb 21, 1:15=A0am, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
> Try it with this modification:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 =A0If Target =3D Range("E3") And Target <> "" Then
> =A0 =A0 =A0 If Range("B2").Value > "" Then
> =A0 =A0 =A0 =A0 =A0Range("A8").Select
> =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0Range("B2").Select
> =A0 =A0 =A0 End If
> =A0 =A0End If
>
> "JLGWhiz" <JLGW...@cfl.rr.com> wrote in message
>
> news:%23Kv$dyosKHA.5356@TK2MSFTNGP02.phx.gbl...
>
>
>
> > Need to modify the code.
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > =A0 If Target =3D Range("E3") Then
> > =A0 =A0 =A0If Range("B2").Value > "" Then
> > =A0 =A0 =A0 =A0 Range("A8").Select
> > =A0 =A0 =A0Else
> > =A0 =A0 =A0 =A0 Range("B2").Select
> > =A0 =A0 =A0End If
> > =A0 End If
>
> > It sees the formula as greater than zero, but not the empty string.
>
> > "robzrob" <robz...@hotmail.com> wrote in message
> >news:5f926433-025c-4103-973d-38aa7958a16b@b7g2000yqd.googlegroups.com...
> >> I'm inputting data in E3 and after I hit enter I want B2 to be
> >> selected. =A0But only for E3 and only in one worksheet. =A0And possibl=
y a
> >> further step... =A0Sometimes the formula in B2 has picked up data from
> >> another place depending on what value was entered in E3. =A0If it's do=
ne
> >> this then I won't need to go to B2, I'd want to go to A8 after E3
> >> instead of B2.- Hide quoted text -
>
> - Show quoted text -

Still won't go.  Strange, 'my' macro has more than one line of code in
it which selects E3, but it executes some of those, but stops at one
particular one.

Having this macro isn't vital, so don't spend time on it if there are
more interesting things about.  Thanks for your help, I've learnt a
few things from it.  I'll have a think and a fiddle and see what I can
come up with.
0
robzrob
2/21/2010 11:17:43 AM
Reply:

Similar Artilces:

User Defined Cells counting
Im tryin to find out how could I count a user defined cells (6 rows) containing a string (ex. 2435 Smith Dr) using a formula. Currently, I'm entering the count manually and I know there's a solution how to automate the counting. If the cell is empty the value to be returned for that cell must be 0 and if it contain the string, its value is = 1..This is how I'm approacing it but it doesn't work using the IF function....Thanks I'm not sure what you are trying to do, but you might look at the FormulaExists function. -- Mark Nelson Office Graphics - Visio Microsoft Co...

Timesheet week in Data Analysis
I am using Project Server 2007 and my work week is Monday thru Sunday. However, when I go to Data Analysis, the timesheet view only shows Monday thru Saturday. How can I reconfigure this to show Monday thru Sunday? Thanks, Dots Hi Dots, Check this link: http://www.pmtoolbox.com/project-management-news/project-server-2007-would-you-rather-start-your-data-analysis-week-on-a-monday.html It might assist with setting up week days in data analysis section "Dots" wrote: > I am using Project Server 2007 and my work week is Monday thru Sunday. > However, when...

How can i dived a cell in excel
I have a very large spreadsheet to work on and i have to have 2 colums one with a number and one with a name how can i move all the other names into a colum without having to do each one individually???? Hi maybe try 'Data - text to <columns' -- Regards Frank Kabel Frankfurt, Germany "LauraCrosssan" <LauraCrosssan @discussions.microsoft.com> schrieb im Newsbeitrag news:298EFEFE-CAE2-458F-96C2-CEF4F1C219F2@microsoft.com... > I have a very large spreadsheet to work on and i have to have 2 colums one > with a number and one with a name how can i move all the ...

Formatt cell
In Excel 2007 I am trying to select an entire column to do the same thing for each line. Column c is hours worked, column d is pay per hour and I want to have column e be the total $ for that day. I don't want to add the formula =product(c3:d3) and so on each time to column e. How do I make column do this atomically each line? Thanks in advance. Bob. I'll answer this for 2003. Enter your formula. Select the cell. There should be a little box in the lower RH corner. Drag it down to the remainder of the cells you want to populate. HTH, Barb Reinhardt "bob b...

creating a file of particular extension
hello friends, i am using ms access 2003, vb6. i want to create a file of extension ".gms" . how can i do this? how can we create a file of particular extension? thanq ...

Copying locked cells
I have a ss with formula in locked columns and the whole thing protected to stop the formula being altered. When the user comes to the last row in which there is a formula she needs to copy this last row and paste into however many rows she needs to complete her figures. However the protection is not allowing the paste function to work. I know this is probably quite basic but your forum has taught me that I know very little about Excel. Maybe you could give the user a macro that would copy the row above (including any formulas). If you want to try, start at David McRitch...

Where Do Messages Go??
If there isn't a kink in my program and it's behaving as it should, I hope someone can answer my questions. Messages disappear. Example: I e-mailed a friend, who e-mailed me back. When I answered that response, I could find no trace of it in "sent mail" or anywhere else, and the item in my inbox maintained its appearance as unread mail. If I click "mark read" it disappears altogether. I've lost several messages this way. Also I seem to be unable to drag a message from one folder to another. It gets lost in the transporter, never to be seen agai...

Formulas in a cell
Hi, new the the forum.... :) I needed some help regarding formulas and text in one cell. What i'm trying to do is develop a simple formula to calculate a simple number. The formula i have is: =(R3*12)-((R3*12)*0.1) But the problem (or myabe not a problem :( ) is that i need it to show as such: $103.57;1;NO;Case Price Is it possible to have a formula and text in the same cell, if so, how? :confused: Any help would be appreciated. Thanks. Saurabh -- sabrol ------------------------------------------------------------------------ sabrol's Profile: http://www.excelforum.com/mem...

how do i allow fields to be either input or output in excel?
i am trying to build a series of financial calculators in excel. Each has 3-6 fields and 1 output field. What i want to do is allow users the flexibility to leave whichever field blank that they wish and the model will solve for that blank field. so the same model can be used a number of different ways and to solve for any of the possible variables. i see this feature in online calculators but i don't know how to set up my models this way. Any help would be greatly appreciated. Thank u! ...

Where do the Archives Go?
I cannot find the information Outlook claims to have archived for me. Yes I know how to display the archived calendar, but it's blank. Any ideas? Thanks! I use XP, and Office 2007. "6Sigma 5s No Clue" <6Sigma5sNoClue@discussions.microsoft.com> wrote in message news:B239B721-DCF1-4337-947D-B325D4BF0E4A@microsoft.com... >I cannot find the information Outlook claims to have archived for me. Yes I > know how to display the archived calendar, but it's blank. Any ideas? > Thanks! > > I use XP, and Office 2007. Why do you think Outook is arch...

How to hide a cell if a formula returns no value?
HI again all, what I'm after this time is a way to hide a cell by using a formula. This is pretty much solely for formatting reasons so that the sheet can be cut and paste into word from excel without empty cells coming up. I need something like this; cell a1 contains "whatever is entered into cell a1" in cell a2 =IF(a1="",Hide cell a2,a1) not an overly useful example I know, but hopefully you guys get the jist of what I mean, Cheers, =IF(A1="","",A1) VBA Noo -- VBA Noo ----------------------------------------------------------------------...

storing historical data in Access
Hi. I am creating an Access database to store information about employees, such as their job title and address info. However, I also need to store their historical job title and address info as it changes. I have other tables in the database to store info on their different benefit plans. In the employee info table, I have their Employee ID (the number that the company assigns to each employee) as the primary field. So, it will not let me add duplicate entries for each employee. I thought about making the primary key an autonumber field but I need the primary key to be the employee ID so...

Price/cost fields need to be able to go beyond 5 decimals.
When we receive product it comes in $ per metric ton, which we convert to $ per lb.. We sometimes receive product in millions of lbs. in one line item, when we convert from $/MT to lb./MT the limit of 5 decimal places in a currency field causes pretty lare rounding errors to occur in our extended cost. This causes our posted cost in GP to be different than what we actually pay our vendors. The ability to extend beyond 5 decimals in the currency field would solve the problem, but for now it is not possible in GP. ---------------- This post is a suggestion for Microsoft, and Microsof...

Multiple calendars, how to choose which one meetings go into
Hi I have 3 calendars setup in outlook XP , when someone sends me a meeting request how can i change which calendar the meeting goes into, at the moment they all go into what i guess is the default calendar which i want to change to a different one any ideas? thanks Chris You really can't change it. Outlook treats certain folders special. "Chris S" <fred@fred.com> wrote in message news:43cf7167$0$23294$db0fefd9@news.zen.co.uk... > Hi > > I have 3 calendars setup in outlook XP , when someone sends me a meeting > request how can i change which calend...

Go to page
I have a long document and need to go the certain pages without having to scroll down. How do I go to a particular page? Thanks This is a multi-part message in MIME format. ------=_NextPart_000_0040_01CAEE07.56832EE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Press Ctrl+G to open the GoTo dialog, and enter the page number. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. "richard" <rmk@wo...

Open a workbook and get data from it
Hello I'm working a project where I've got a list of names in one spreadsheet, and I need to pull corrasponding data from another spreadsheet. The concept is simple.... get a name from spreadsheet1, then go into spreadsheet2, find that username, and copy the row over to spreadsheet1. At least that is how you do it manually. In excel VBa, from one workbook, how do I a open another workbook and worksheet then get the correct data i need? Modify to suit Sub OpenFileFindNameCopyToThisFile() Workbooks.Open Filename:="C:\sourcefoldername\sourcefilename.xls&q...

Bring all data from a cell to a listbox inside a form
Dear all, I have a cell with data separeted by a ";" caracter, for example: Item1;Item2;Item3;Item4 So when I initialize my userform I would like that my listbox "lstTest" load the data in the cell (1,1) But I need the data in lines (the separation is the ";" caracter). For example: lstTest (looks like) Item1 Item2 Item3 Item4 Thank in advance!!!!!! Andr=E9. If you're using xl2k or later: Option Explicit Private Sub UserForm_Initialize() Dim myArr As Variant myArr = Split(Worksheets("sheet1").Range("a1").Value, "...

Is it possible to synchronize data from an SQL table to an Exchange Server
Is it possible to have a replication or synchronization between contact info stored in an SQL db's table and the Contacts or Public folders of an Exchange Server. Sure - programmatically, it can be done. Check out MSDN for more info. -- Nick Cavalancia MCSE/MCT/MCNE/MCNI nickc@remove-comsphere.com Comsphere - "Where Knowledge Transfer is Key" Web: http://www.comsphere.com Ph: 954.275.0114 Fax: 954.791.6763 "D Gauthier" <dgauthier_nospam@nospam-liaison-intl.com> wrote in message news:A5wZb.3163$Hg4.1407@chiapp18.algx.net... > > Is it possible to have...

How Excel converts % sign in a cell and use in formula
I am trying to figure out a formula in Excel that I know is simple, but it's been years.... =(B3+(B3*$F$8))*(1+$F$9) I understand about the order of operations and what has to happen first and the absolute cell reference. I'm confused about what the 1 is in the (1+$F$9)...the info in cell F9 is sales tax and is written as 8.25%. Thanks in advance for the help. -- Peggy Duncan, Author Conquer Email Overload with Outlook http://www.PeggyDuncan.com Worksmart wrote: > I am trying to figure out a formula in Excel that I know is simple, but it's > been years.... > > ...

Here we go again
Well, once again CashEdge is not connecting/syncing with Money and MSN Money. I guess I won't run right out to buy Money 2007. Even if the software were an improvement over M06, it cannot be any better than the weakest link in the sync chain. A known problem, David -- and not unique to the version of Money. Look at http://moneycentral.msn.com/common/networkstatus.asp -- Michael Gordon MVP "David Haynes" <nospam@nowhere.com> wrote in message news:eNopg0UzGHA.1252@TK2MSFTNGP04.phx.gbl... > Well, once again CashEdge is not connecting/syncing with Money and MSN ...

Enter date in one cell, and the week ending date [Sunday] appears in the "W/E" cell
I have an XL 2003 workbook that I enter the date an event occured in cell C7. I want to make another cell [C2]display the week ending date [Sunday], based ono the date entered in cell C7. How can I accomplish this? Thanks, Tonso =7-WEEKDAY(C7,2)+C7 Daniel > I have an XL 2003 workbook that I enter the date an event occured in > cell C7. I want to make another cell [C2]display the week ending date > [Sunday], based ono the date entered in cell C7. How can I accomplish > this? > > Thanks, > > Tonso hi, =IF(WEEKDAY(C7)=1,C7,C7+(8-WEEKDAY(C7))) -- isabelle On ...

cell reference question
I'm as new to Excel as can be, so be gentle. When making reference to a certain cell (say, Cell J60, containing a total), what's the difference between using =J60 and $J$60. I've read about this in the Microsoft KB but I don't have enough experience to understand it. Can you put it in as simple terms as possible for me? A million Thank You's! "Island Girl" <IslandGirl@discussions.microsoft.com> wrote in message news:20A76CA9-F60A-4E7C-BB4D-22A38B6FE68A@microsoft.com... > what's the difference between using =J60 and $J$60 $J$60 is what is...

mail merge has the open data source not available
publisher 2000 under mail merge > open data source. only create publisher address list or edit publisher address list. all other commands are greyed out and unavailable. I have a text box and select that before looking for the command. Have you created a Publisher list? Where is your data source? What exactly are you trying to do? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Sammc2" <Sammc2@discussions.microsoft.com> wrote in message news:C6A6DA69-6548-4A7C-915E-6A2A79F15D9A@microsoft.com... > publisher 200...

Not all emails going out??
For the last week or so, send few, my emails have not been going out, or not going out to all parties I access from my address book. Just tested by sending one to myself...went fine. Sent second to a single party and cc myself. Did not receive my cc in outlook, but it did show up in my iphone. Tried a third to a group of people...through a single address book contact, and nothing happened. I am strictkly novice, but have not had this problem before. Any ideas??? -- Thanx! Wilson Kobel;110315 Wrote: > For the last week or so, send few, my emails have not been going...

Forced new line in a cell: Excel
Hello, I wanted to start a forced new line after typing down the texts which wraps down automatically in a given excel cell. Pressing [Enter] just like any other Microsoft Office documents would take me to the next cell as selected! Googling some online help provided a [Alt]+[Enter] keystroke would serve the purpose. But, that's not happening, is that a bug for excel? But, it works in my coworker's pc! -- Raqueeb Hassan Bangladesh So, it's not an Excel bug - more likely to be something to do with your PC. Try swapping the keyboard with your co-worker to check that. Pete On J...