[RESOLVED] Macro to create Hyperlink basing from cell address value

[leebrooke]

Hi,

I've looked in most threads related to creating macro for hyperlinks but i couldn't find one that can answer to my queries. Below is my problem.

I have two sheets, 1st sheet is a calendar, which contains Months and days. the 2nd sheet is only having 1 column with "Locate Date" as heading. Each of the rows within that column has values like L16, B25, B19, D9, D4, E10 etc.

i want to create a macro that will go through each of these values, and will create hyperlink with respect to sheet 1. For example, if in Sheet 2, cell1 has L16 value, then i want to make a hyperlink on cell1 which reference it's value to that in Sheet 1. Thus, when hyperlink is made, when i click on cell 1, it will go to Sheet 1 with address L16.

The original spreadsheet i have has hundreds of values in "Locate Date" column. It would take time for me to manually create a hyperlink in Excel if i have to do it one by one.

Please help me with this. I'd really appreciate your help. Attached is my sample workbook.

[Help321]

Hi

Try this:

Dim i As Integer
    
    i = 4
    For i = 4 To 10
        Range("A" & i).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Sheet1!" & Range("A" & i).Text, TextToDisplay:=Range("A" & i).Text
    Next i

That will start at cell A4, as you've already done A3 and finish at A10. Amend it as required for your real spreadsheet.

Cheers

[leebrooke]

Hi

Try this:

Dim i As Integer
    
    i = 4
    For i = 4 To 10
        Range("A" & i).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Sheet1!" & Range("A" & i).Text, TextToDisplay:=Range("A" & i).Text
    Next i

That will start at cell A4, as you've already done A3 and finish at A10. Amend it as required for your real spreadsheet.

Cheers

Thanks a lot :-) This is what i really need. I tried to use the same concept but the one thing i couldn't do is how to implement it in ActiveSheet.Hyperlinks.Add method. I kind of confused what to put in "SubAddress" as i am getting an invalid link in my previous code.

Thank you so much again for the help :-)