Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[cmr72]

Hello All,

I had the good fortune of getting help on the code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Or IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("3:49")) Is Nothing Then
Range("R1").Value = Target.Value
End If
End Sub

The cells look like:
L ABT JNJ 1.417 1.39

So currently if I selected JNJ, R1 becomes JNJ and then in H1 I am using INDEX(MATCH) to look up ABT on another worksheet. The opposite will be true if I select ABT.

I'd like to not have dependency on the other worksheet and if there is more than one instance of either target I believe the INDEX(MATCH) will resolve the first instance (or last?). I am hoping to make Range("H1").Value = Target.Value equal whatever is either to the right or left of the target.

My initial guess is that I need an argument to use the cell to left of the target if the cell to the right of the target is numerical, and the cell to the right of the target if the cell to the right of target is non-numerical.

I don't know how to put this into code since I am a novice here.

Any additional help is much appreciate.

 

[Snakehips]

cmr72,

Not tested, but does the following do what you are asking?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Or IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("3:49")) Is Nothing Then
Range("R1").Value = Target.Value
If Not IsNumeric(Target.Offset(0, 1).Value) Then
Range("H2").Value = Target.Offset(0, 1).Value
Else:
Range("H2").Value = Target.Offset(0, -1).Value
End If
End If
End Sub

Hope that helps.

 

[cmr72]

That is exactly it. Thank you very much. I had an idea about using Target.Offset, but did not know the correct syntax to search for. Also, if you wouldn't mind, does Target.Offset() behave entirely different than OFFSET() in excel? I am trying to not have any volatile functions in my workbook.

 

[Useful]

That is exactly it. Thank you very much. I had an idea about using Target.Offset, but did not know the correct syntax to search for. Also, if you wouldn't mind, does Target.Offset() behave entirely different than OFFSET() in excel? I am trying to not have any volatile functions in my workbook.

Hi!

'[B5].Offset(a, b).Select
'if a,b positive say = 1 then
[B5].Offset(1, 1).Select 'This code will select the C6
'if a,b negative say = -1 then
[B5].Offset(-1, -1).Select 'This code will select the A4
'hence
'I negative mean that rows going to up but columns going to left
'II positive mean that rows going to down but columns going to right

 

[Snakehips]

cmr72,

I can't give you a definitive answer re the volatility. It's certainly not putting the function, ongoing, into the cell, rather just using it at the instant of coding, as I would see it.

If you would feel happier then perhaps try this.......

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Or IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("3:49")) Is Nothing Then
Range("R1").Value = Target.Value
MyRow = Target.Row
MyRight = Target.Cloumn + 1
MyLeft = Target.Column - 1
If Not IsNumeric(Cells(MyRow, MyRight).Value) Then
Range("H2").Value = Cells(MyRow, MyRight).Value
Else:
Range("H2").Value = Cells(MyRow, MyLeft).Value
End If
End If
End Sub

Again, untested.

Let me know how you get on.

 

[cmr72]

I am going to stick with your original suggestion. I agree that it is more or less instantaneous at the time of the code running so there should be no volatility, if any, after the code has been executed. I will do some more research on the topic. Thanks again for your help.