# How to combine 2 vba codes in one



## hendrikbez (Jan 3, 2023)

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


----------



## Alex Blakenburg (Jan 3, 2023)

Give this a try:
If you are entering data into columns I or J to trigger this, you would be better off using the Worksheet_Change option to run this, it runs a little less often.


```
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
 
  Select Case Target.Column
    Case 9                 ' Column I
        Set Dest = Range("B:B").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
    Case 10                 ' Column J
        Set Dest = Range("E:E").Find(Target, LookIn:=xlValues, Lookat:=xlWhole)
  End Select
 
  If Dest Is Nothing Then
    MsgBox Target, vbInformation, "Nie Gevind"
  Else
    Application.EnableEvents = False
    Dest.Select
    Application.EnableEvents = True
  End If
End Sub
```


----------



## kvsrinivasamurthy (Jan 3, 2023)

Try this

```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Dest As Range
  If Target.CountLarge > 1 Then Exit Sub
  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 Sub
```


----------



## hendrikbez (Jan 3, 2023)

kvsrinivasamurthy said:


> Try this
> 
> ```
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> ...


Thank you, but when I add this code , it is still not working, the first part for I is working, but for second part J it show pop up with "nie gevind" every where I click.   Ok The data that it is getting worksheet "Tabel van rekeninge"


----------



## hendrikbez (Jan 3, 2023)

Alex Blakenburg said:


> Give this a try:
> If you are entering data into columns I or J to trigger this, you would be better off using the Worksheet_Change option to run this, it runs a little less often.
> 
> 
> ...


Thank you, but when I add this code , it is still not working, the first part for I is working, but for second part J it show pop up with "nie gevind" every where I click.  Ok The data that it use is getting from a other worksheet "Tabel van rekeninge"


----------



## Alex Blakenburg (Jan 3, 2023)

hendrikbez said:


> Ok The data that it use is getting from a other worksheet "Tabel van rekeninge"


Say what !!!
Please explain, I assume the SelectionChange event is in a sheet that is not "Tabel van rekeninge". So why are we looking at a different sheet ?
It is only going to trigger when we move around on the sheet with the code in it.


----------



## hendrikbez (Jan 3, 2023)

Alex Blakenburg said:


> Say what !!!
> Please explain, I assume the SelectionChange event is in a sheet that is not "Tabel van rekeninge". So why are we looking at a different sheet ?
> It is only going to trigger when we move around on the sheet with the code in it.


Ok,
I just looked at my  page, and where wrong. on the page I want to use thids code all the wordfs that are in I (income) and J(Exxpense) are sholwing, so it should be B only an d not E at all, the problem is that one name (eg Tiendes) can be in both I or J, so whewn I click on Tiendes in J it is going to Tiendes in I

Sorry For the confution, I have confuse myself aswell


----------



## Alex Blakenburg (Jan 3, 2023)

I think I am even more confused.
You have Tiendes in columns I and J.
When you click on the word in columns I OR J you want it to find that word in Column B

Does it appear muliple time in B ?
If Yes then how is the code supposed to know which occurence in B you want it to go to ?


----------



## hendrikbez (Jan 3, 2023)

Alex Blakenburg said:


> I think I am even more confused.
> You have Tiendes in columns I and J.
> When you click on the word in columns I OR J you want it to find that word in Column B
> 
> Does it appear muliple time in B ?


Yes if I click on I or J,it is looking for the word in B, and some of the words can be in B 2 times only.


----------



## Alex Blakenburg (Jan 3, 2023)

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


----------



## hendrikbez (Jan 3, 2023)

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 (Jan 3, 2023)

Alex Blakenburg said:


> 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 (Jan 3, 2023)

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 (Jan 3, 2023)

Using1021, will change it in account profile


----------



## Alex Blakenburg (Jan 3, 2023)

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 (Jan 3, 2023)

Alex Blakenburg said:


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


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


----------



## Alex Blakenburg (Jan 3, 2023)

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


----------



## hendrikbez (Jan 3, 2023)

Alex Blakenburg said:


> 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 (Jan 3, 2023)

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


----------



## kvsrinivasamurthy (Jan 3, 2023)

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 (Jan 3, 2023)

kvsrinivasamurthy said:


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


----------

