Better than hyperlink

Question

I want to put on one of my worsheets (have many entries on it) to do the following.
on one worksheet, every _+ 9 rows I have a name, from B9: to B968 (there will be more added in future.
The names I get from "Chart of Accounts", and need to add them in "GL-MO 10"
There are two diffrent options (inkomste) and (uitgawes)

I am trying it with hyperlink, but the names can change or new ones added, is there a better way to do it with code.

Answer 1

Change

Set Target = Intersect(Target, Range("I:I"))

to

Set Target = Intersect(Target, Range("I:L"))

Answer 2

Right-click on the sheet tab
Choose "View Code"
Paste in the code below
Close the VBA editor
Select a cell in column I

Does it work as expected?

Andreas.

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, "Not found"
Else
Application.EnableEvents = False
Dest.Select
Application.EnableEvents = True
End If
End Sub

Answer 3

Thank you it is working, but if I split the names in 4 columns (to see it on the screen) (I,J,K and L) wich line do I have to change

Answer 4

Thank you

Here is the file

https://mega.nz/file/PipkGYCK#8kGmv1TaNLNIEeOm0uPq66tAJSGpyyWdHRlFvdQrNp0

Answer 5

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.