Macro toupdate hyper link

[luvbite38]

Hi Guys,

I hope someone can help me out here.

Is there a way to make following two changes to hyperlinks

1- change the Text to Display to a numbers starting from 1 to onwards (1, 2, 3....n).
2- Also change the "Look in" option. This should be based on a cell value (Sheet1.cellA1.value). This cell contains the name of the folder.

I hope it makes sense?

Thanks in advance

 

[RickXL]

The following should work:

Sub myTest()
    Dim i As Long
    Dim ws As Worksheet
    
    ' File or URL is in A1
    ' e.g. [url=http://www.google.com]Google[/url], C:\myfile.txt, [URL]file://C:\myfile.txt[/URL]
    ' Displayed name is: 99
    ' Hyperlink appears in B1
    
    i = 99
    Set ws = Sheet1
    ws.Range("B1").Formula = "=HYPERLINK(""" & ws.Range("A1") & """, " & i & ")"
    
End Sub