VBA Hyperlink

[tlrobinson512]

Good Morning All:

I am hoping someone will have an answer for me on my latest obstacle. I am trying to do a couple of different things:

1. I am running a simple Find Macro that takes the value (in this case, a reference number) from cell H7 on sheet1, and searches through a series of additional sheets in the workbook for the reference number. Once the reference number is found, what I'd like to do is to hyperlink H7 to that active cell. I have been able to hyperlink the active cell to H7, but not the opposite way.

This is where I am currently

'***************** FIND REFERENCE NUMBER ******************

If Trim(Val1) <> "" Then
With Sheets("Jamie").Range("A:A")
Set Rng = .Find(What:=Val1, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

* I can't get this to do the opposite. Hyperlink "Dashboard" Range H7 to the activecell from the above Find. function.

'***************** HYPERLINK FOUND VALUE ******************

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Dashboard!H7"

If Not Rng Is Nothing Then
Application.Goto Rng, True

 

[dchaney]

Re: VBA Hyerlink

Try this,

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _"Dashboard!H7"


wsCurrent = ActiveSheet.Name
CellLink = Selection.Address


Sheets("Dashboard").Hyperlinks.Add Anchor:=Cells(7, 8), Address:="", SubAddress:="'" & wsCurrent & "'!" & CellLink

 

[tlrobinson512]

Re: VBA Hyerlink

Thanks DChaney!

Thank you so much for your response! This is still giving me the reverse of what I am trying to accomplish. This does what my current code does. The foundation is built here, but what I need is this, as an example.

My request# is 125777 and is located at Sheets("Dashboard").Range("H7")

I do a Find.Function using the following variable: Val1 = Worksheets("Dashboard").Range("H7").Value

The Find.Function searches through the various sheets for this value (my request#). When that value is found, it goes to that active cell on the sheet found...in this case, Sheets("Krystal").Range("A13")
I need to then Hyperlink H7 to that active Cell. I can get it to hyperlink the active cell to H7, but H7 is the main Dashboard. The sheet "Krystal" is just a reference sheet. Hopefully that makes more sense. Thank again for taking time to review and assist me where you can!

 

[dchaney]

Re: VBA Hyerlink

Are you ALWAYS looking for data from Cell H7 or is it a list of request#'s down column H?

And can you post your entire code?

 

[dchaney]

Re: VBA Hyerlink

Try this,

Val1 = Worksheets("Dashboard").Range("H7").Value

If Trim(Val1) <> "" Then

    With Sheets("Jamie").Range("A:A")
        Set Rng = .Find(What:=Val1, _
        After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)
    End With
    
End If

wsCurrent = ActiveSheet.Name
CellLink = Rng.Address

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range(CellLink), Address:="", SubAddress:="Dashboard!H7"

Sheets("Dashboard").Hyperlinks.Add Anchor:=Cells(7, 8), Address:="", SubAddress:="'" & wsCurrent & "'!" & CellLink