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

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

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.
 
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
 
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.
 
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.






- Show quoted text -
Click to expand...

Perfect. Thank you.
 
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.






- Show quoted text -
Click to expand...

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.
 
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
 
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









- Show quoted text -
Click to expand...

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.
 
Back
Top