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




"robzrob" wrote in message
...
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.

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" wrote in message
...
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.

On Feb 21, 12:25*am, "JLGWhiz" wrote:
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" wrote in message

...



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.- Hide quoted text -

- Show quoted text -

Perfect. Thank you.

On Feb 21, 12:25*am, "JLGWhiz" wrote:
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" wrote in message

...



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

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" wrote in message
...
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" wrote in message
...
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.

On Feb 21, 1:15*am, "JLGWhiz" wrote:
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" wrote in message

...



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" wrote in message
...
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.- 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.