Setting the target of a hyperlink based on first instance of a cell's value found in another sheet

[J Blizzard]

I am trying to type a positive integer into column A of sheet1 and have excel automatically turn that cell into a hyperlink to take me to Column A, Row [that value] of sheet2. So once I type a number, say "5," into sheet1 column A, any row, I want Excel to search sheet2, Column B for the first instance of the number 5 and return that row number to be used in the hyperlink. The reason I want to search for the first instance of what I type is that sheet2 is mostly empty and each value has multiple instances that are grouped together. Going to the first instance of the value will make sure I land at the top of the grouping.

The code I've been given/found/fiddled with right now isn't working and I only partially know what it all does:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("A:A")) Is Nothing) Then
    If IsNumeric("+" & Target.Value) Then 'Excel treats null cells as numeric and creates a link for them. +1 == 1, and + is not numeric.
    rowno = Target.Row
    colno = 1
     Application.EnableEvents = False
    
      Dim lRow As Long
      lRow = Application.WorksheetFunction.Match(Target.Value, Range("Screenshots!B:B"), 0)
     
      With ActiveSheet
           celno = "'Screenshots'!B" & lRow
                  .Hyperlinks.Add Anchor:=.Range(Cells(rowno, colno), Cells(rowno, colno)), _
                   Address:="", _
                  SubAddress:=celno
        End With
    End If
End If
Application.EnableEvents = True

End Sub

Most of this code comes from a previous thread, which solved the problem of how to link from sheet 2 to sheet 1. If there is a simpler way to do this then I'm all ears! Thanks in advance for any help with this.

 

[offthelip]

This appears to be an extension of your previous question:Help writing a hyperlink reference to another worksheet based on a number in a neighboring cell
I think you should adhere to the rules and have continued with the same thread rather than starting a new thread. I believe the solution you are asking for is not the optimum solution ( but I could be wrong) as I suggested in my response to your previous question:
"you can use the same technique to create the link back too. "
In your previous question you were entering a number on the "Screenshots" page and it created a link to the "Trade log" page. You can use exactly the same macro to create the link back from the Trade log page to the screen shots page WITHOUT needing to type a number in on the Trade log page. Is this what you want?

 

[offthelip]

try this code in your "Screenshots" worksheet it will put BOTH links in at the smae time. you just type the trade number in in column B of the screenshots worksheet and put a link to the trade log and the link back from the trade log.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("B:B")) Is Nothing) Then
    If IsNumeric("+" & Target.Value) Then
    rowno = Target.Row
    colno = 2
     Application.EnableEvents = False
       With ActiveSheet
           celno = "'Trade Log'!A" & Target.Value + 2
                  .Hyperlinks.Add Anchor:=.Range(Cells(rowno, colno), Cells(rowno, colno)), _
                   Address:="", _
                  SubAddress:=celno
        End With
       With Worksheets("Trade Log")
                  celno = "Screenshots!B" & rowno
                   rowno2 = Target.Value + 2
                   colno2 = 1
                  .Hyperlinks.Add Anchor:=.Range(.Cells(rowno2, colno2), .Cells(rowno2, colno2)), _
                   Address:="", _
                  SubAddress:=celno
        End With
    
    
    End If
End If
Application.EnableEvents = True

End Sub

 

[J Blizzard]

I see what you mean about Rule #12 but I think this question is different enough to warrant its own thread. If the answer is similar enough then it should be added onto the existing thread. However I think the two solutions are very different.

For this thread, I cannot use the code that you've written because it assumes my links are in a regularly repeating pattern. In my Screenshots worksheet, the links are irregularly-spaced. (In hindsight I should've specified this, but I thought it was clear from the screenshot of the other thread which you solved) I might have 1, 2, or 3 screenshots per trade, and each screenshot takes up 27 and a fraction of a line. In other words, trades 1, 2, and 3 correspond with lines 3, 4, and 5 in the Trade Log sheet, but on the Screenshots sheet they correspond with lines 4, 31, and 85. There is no regular pattern, so I won't know what number to put in the code ahead of time. If I had 1 screenshot every 27 lines or something like that, then of course it would be trivial to use the provided code to be 27x+2.

 

[offthelip]

You are misunderstanding the code, what you have explained is exactly what I anticipated, the way it should work is , you paste a screenshot on the screen shot page, and say the first screen shot is row 27, type 3 into B27, you then get alink from screenshot B27 to tradelog A5 AND a link from tradelog A5 to screenshot B27, Next trade: add screenshot and assume it ends up at B112 you put 4 into B112 and you get link to tradelog A6 and a link from tradelog A6 to screenshot B112. I believe this is exactly what you want, The only numbers you type in are successive trade numbers and you will know what those are . the code i have just updated goes in the SCREENSHOT page, you don't need any code in the Tradelog page, it is all done when you enter the trade number with the screen shot

 

[J Blizzard]

You're exactly right: I've misunderstood your code and not realized that you were a step ahead of me. That's why I'm the one asking for help lol. Thank you for this!

The only problem left is when I create multiple screenshots, the link in sheet1 goes to the last screenshot in sheet2. I want the link in sheet1 to always go to the first screenshot of that trade. So here's what it would look like manually:

Input data into sheet1.
Input picture and trade # into sheet2. A link is created from sheet2 to sheet1 and another link is created from sheet1 to sheet2, screenshot 1. (works as intended)
Input another picture and same trade # into sheet2. A link is created from sheet2 to sheet1 (works as intended) but then excel creates a link from sheet1 to sheet2, screenshot 2. This is the problem.

Is there a way I can put the link creation code inside an IF statement that tests if there is already a link? I'm not sure what that logical test would look like. I tried the following:

       With Worksheets("Trade Log")
                  celno = "Screenshots!B" & rowno
                   rowno2 = Target.Value + 2
                   colno2 = 1
                    If (Range("'Trade Log'!A" & rowno2).Hyperlinks.Count = 0) Then
                        .Hyperlinks.Add Anchor:=.Range(.Cells(rowno2, colno2), .Cells(rowno2, colno2)), _
                        Address:="", _
                        SubAddress:=celno
                    End If
        End With

but now it never creates a link at all.

 

[offthelip]

You haven't quite explained what you are doing; Are you entering the same number more than once in column B of the Screenshots worksheet? ie when you have three screenshots for trade no.2 do you "2" into column B opposite each screen shot? if this is the problem one way round this is to put the number "2" in column B for the first screen shot and then put "2" into the column C for the remaining screen shots, another way is to put "2" in the first screen shot and 2A in the 2nd and 2b in the third etc., ( the isnumeric should prevent creation of the link)

 

[J Blizzard]

Yes sorry, I am putting the same number multiple times for successive screenshots of the same trade. I'd prefer to keep it this way instead of doing 2A, 2B, or using column C to keep everything visually easy for the user. My goal is to make the user's job extremely simple.

 

[offthelip]

Try this code: it detect whether a link already exist in the cell in Trade Log, so it won't create a new one if one already exists

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("B:B")) Is Nothing) Then
dim h As Hyperlink
    If IsNumeric("+" & Target.Value) Then
    rowno = Target.Row
    colno = 2
     Application.EnableEvents = False
       With ActiveSheet
           celno = "'Trade Log'!A" & Target.Value + 2
                  .Hyperlinks.Add Anchor:=.Range(Cells(rowno, colno), Cells(rowno, colno)), _
                   Address:="", _
                  SubAddress:=celno
        End With
       With Worksheets("Trade Log")
                  celno = "Screenshots!B" & rowno
                   rowno2 = Target.Value + 2
                   colno2 = 1
                   hypexists = False
                   For Each h In .Hyperlinks
                       addt = h.Range.Cells.Row
                       If addt = rowno2 Then
                       hypexists = True
                       End If
                   Next h
                   If Not (hypexists) Then
                  .Hyperlinks.Add Anchor:=.Range(.Cells(rowno2, colno2), .Cells(rowno2, colno2)), _
                   Address:="", _
                  SubAddress:=celno
                  End If
        End With
   
   
    End If
End If
Application.EnableEvents = True

End Sub

 

[J Blizzard]

It's perfect! Thank you so much! Walking through all these code successions has made me a lot more interested in VBA. It still looks kind of weird but it also seems like a great tool.