Create hyperlink with VBA

[geotzi]

Hi all,

I have 2 excel sheets (Sheet A and Sheet B) and I would like to assign a VBA code to create a hyperlink in Sheet A. The hyperlink will be created in cells (column B Sheet A) when there is a match for Xi in these two Sheets.

I have created a loop function in order to compare the two columns in Sheet A and Sheet B. I am not sure how is the VBA syntax in order to create the hyperlink in cells Sheet A when there is a match for the Xi variable.

Could you please help me?
Thank you in advance for your help.

Georgia.

 

[Deepak]

Can we have a sample file pls.

 

[geotzi]

Hi,

I can not share the whole document but i have added the VBA code below:

The Hyperlinks.Add statement doesn't work and I am still wondering what is the right syntax.

Sub Inserthyperlink()
    Dim id As String
    Dim i As Integer, j As Integer, Opptotal As Integer

    Opptotal = Worksheets("Source").Cells(2, 5).Value
    For i = 2 To Opptotal
        id = Worksheets("SheetA").Cells(i, 2).Value
        n=Worksheets("SheetB").Range("D:D").Cells.SpecialCells(xlCellTypeConstants).Count
        For j = 2 To n
            If id = Worksheets("SheetB").Cells(j, 4).Value Then
               Hyperlinks.Add Worksheets("SheetA").Cells(i, 2), Worksheets("SheetB").Cells(j, 4)
            End If
        Next j
    Next i
End Sub

Thanks,

Georgia.

 

[Deepak]

Sorry! But i failed to understand your req. I have asked for a sample wb with around 100 rows of data.

 

[Stevie]

Hi geotzi,
hyperlinks work like this:

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("a5"), _
Address:="http://example.microsoft.com", _
ScreenTip:="Microsoft Web Site", _
TextToDisplay:="Microsoft"
End With

as can be seen here:
https://msdn.microsoft.com/en-us/library/office/ff822490.aspx

That code would allow you to click the word 'microsoft' and your browser would open on example.microsoft.com.

What is it you are trying to do?

 

[NARAYANK991]

Hi Georgia ,

Try this :

Sub Inserthyperlink()
    Dim id As String
    Dim i As Integer, j As Integer, Opptotal As Integer

    Opptotal = Worksheets("Source").Cells(2, 5).Value
    For i = 2 To Opptotal
        id = Worksheets("SheetA").Cells(i, 2).Value
        n = Worksheets("SheetB").Range("D:D").Cells.SpecialCells(xlCellTypeConstants).Count
        For j = 2 To n
            If id = Worksheets("SheetB").Cells(j, 4).Value Then
              Worksheets("SheetA").Hyperlinks.Add Worksheets("SheetA").Cells(i, 2), "", "#" & Worksheets("SheetB").Name & "!" & Cells(j, 4).Address, , id
            End If
        Next j
    Next i
End Sub

Narayan

 

[geotzi]

Thank you for your responses. I will implement your suggestions.

Georgia

 

[geotzi]

Hi Georgia ,

Try this :

Sub Inserthyperlink()
    Dim id As String
    Dim i As Integer, j As Integer, Opptotal As Integer

    Opptotal = Worksheets("Source").Cells(2, 5).Value
    For i = 2 To Opptotal
        id = Worksheets("SheetA").Cells(i, 2).Value
        n = Worksheets("SheetB").Range("D:D").Cells.SpecialCells(xlCellTypeConstants).Count
        For j = 2 To n
            If id = Worksheets("SheetB").Cells(j, 4).Value Then
              Worksheets("SheetA").Hyperlinks.Add Worksheets("SheetA").Cells(i, 2), "", "#" & Worksheets("SheetB").Name & "!" & Cells(j, 4).Address, , id
            End If
        Next j
    Next i
End Sub

Narayan

thank you very much! this works perfect!