How to combine 2 vba codes in one

[hendrikbez]

I am new to VBA in excel

This first part of the vba code worked, but I have changed it, as when run it and have a enrty with same name on column B it show the same name that it fitst get,
so now I have make 2 columns, but doi not know how to change it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Dest As Range
  If Target.CountLarge > 1 Then Exit Sub
  Set Target = Intersect(Target, Range("I:I"))
  If Target Is Nothing Then Exit Sub
  Set Dest = Range("B:B").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
  If Dest Is Nothing Then
    MsgBox Target, vbInformation, "Nie Gevind"
  Else
    Application.EnableEvents = False
    Dest.Select
    Application.EnableEvents = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Dest As Range
  If Target.CountLarge > 1 Then Exit Sub
  Set Target = Intersect(Target, Range("J:J"))
  If Target Is Nothing Then Exit Sub
  Set Dest = Range("E:E").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
  If Dest Is Nothing Then
    MsgBox Target, vbInformation, "Nie Gevind"
  Else
    Application.EnableEvents = False
    Dest.Select
    Application.EnableEvents = True
  End If
End Sub

 

[hendrikbez]

So what do you want it to do if it appears more than once ?

If I Click on I it must go to the first time the word appears, and if I click on J it must go to the second time thw ord appears

 

[Alex Blakenburg]

What version of Excel are you using (please also change your Account Profile to show that) ?
If you are on MS365 or on 2021 are all your users on that version ie can we use XLookup.

 

[hendrikbez]

Using1021, will change it in account profile

 

[Alex Blakenburg]

Here is another version using Find.
At the moment if there is only one instance in column B then both column I and J will go there.
If there are 2 instances in column B, Column I will go to the 1st and column J to the second.

Let me know if you want it to return not found if you select column J and there is only one occurence in column B.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Dest As Range
  If Target.CountLarge > 1 Then Exit Sub
  Set Target = Intersect(Target, Range("I:J"))
  If Target Is Nothing Then Exit Sub

  Set Dest = Range("B:B").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
 
  If Target.Column = 10 Then
    Set Dest = Range("B:B").FindNext(after:=Dest)
  End If

  If Dest Is Nothing Then
    MsgBox Target, vbInformation, "Nie Gevind"
  Else
    Application.EnableEvents = False
    Dest.Select
    Application.EnableEvents = True
  End If
End Sub

 

[hendrikbez]

Here is another version using Find.
At the moment if there is only one instance in column B then both column I and J will go there.
If there are 2 instances in column B, Column I will go to the 1st and column J to the second.

Let me know if you want it to return not found if you select column J and there is only one occurence in column B.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Dest As Range
  If Target.CountLarge > 1 Then Exit Sub
  Set Target = Intersect(Target, Range("I:J"))
  If Target Is Nothing Then Exit Sub

  Set Dest = Range("B:B").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
 
  If Target.Column = 10 Then
    Set Dest = Range("B:B").FindNext(after:=Dest)
  End If

  If Dest Is Nothing Then
    MsgBox Target, vbInformation, "Nie Gevind"
  Else
    Application.EnableEvents = False
    Dest.Select
    Application.EnableEvents = True
  End If
End Sub

Thank you Alex, is is working fine for me now, and thank you for your patience with me.

 

[Alex Blakenburg]

No problem. Glad we could help.
PS: Why "Nie" and not "Niet" ? or is it not Dutch

 

[hendrikbez]

No problem. Glad we could help.
PS: Why "Nie" and not "Niet" ? or is it not Dutch

I am Afrikaans Niet is for Dutch, I can understand if someone is typing text and when a dutch person speaks slowly, i can understand him/her

 

[Alex Blakenburg]

I thought that was the most likely. My Dutch is very rusty though and not up to an actual conversation.

 

[kvsrinivasamurthy]

This works

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Dest As Range
  If Target.CountLarge > 1 Then Exit Sub
  If (Target.Column = 9 Or Target.Column = 10) And Target <> "" Then
  
  If Not Intersect(Target, Range("I:I")) Is Nothing Then
  Set Dest = Range("B:B").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
  ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
  Set Dest = Range("E:E").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
  End If
    
  If Dest Is Nothing Then
    MsgBox Target, vbInformation, "Nie Gevind"
  Else
    Application.EnableEvents = False
    Dest.Select
    Application.EnableEvents = True
  End If
  End If
End Sub

 

[Alex Blakenburg]

This works

It does not take into account the additional information provided. Both find statements need to be looking at column B (not column E) and if the Target is in Column J ideally the 2nd occurence (should it exist) in column B should be selected.