Hyperlink Macro Loop with Error Handling????

[holt3130]

OK, for some reason (someone probably knows why) when I use the macro below to add a hyperlink to all cells in one column of a table the loop will reach a value that is just numbers and spit out an error.

the values in the cells are the names of other worksheets in the file. and the values are generated with a loop that changes the values of the cells in one column to the names of the sheets in the workbook. most of the sheet names are a combination of numbers and letters like: "SP1586". the errors come from cells containing names like "601". I dont know why...

so I need help with one of two things, either:

add code to skip errors and highlight the cell that made the error.
OR
eliminate the error by fixing the code, if possible.

Sub addlink()


    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
    SubAddress:="'" & ActiveCell.Value & "'!A1", TextToDisplay:=ActiveCell.Value
    
End Sub

Sub looplink()


    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 5 To FinalRow
    Cells(i, 1).Select
    Call addlink
    Next i
    


End Sub

 

[mole999]

Sub looplink()

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 5 To FinalRow
    Cells(i, 1).Select
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveCell.Value & "'!A1", TextToDisplay:=ActiveCell.Value
    Next i
    
End Sub

 

[FormR]

Try:

Sub addlink()
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
    SubAddress:="'" & ActiveCell.Value & "'!A1", TextToDisplay:=ActiveCell.Text
End Sub

Or better, do away with the selects..

Sub addlink(r As Range)
    r.Hyperlinks.Add Anchor:=r, Address:="", _
    SubAddress:="'" & r.Value & "'!A1", TextToDisplay:=r.Text
End Sub

Sub looplink()
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 5 To FinalRow
        Call addlink(Cells(i, 1))
    Next i
End Sub

 

[holt3130]

I need to be able to run "addlink" outside of the loop. That's the reason for separate macros...

 

[holt3130]

Try:

Sub addlink()
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
    SubAddress:="'" & ActiveCell.Value & "'!A1", TextToDisplay:=ActiveCell.Text
End Sub

I've tried this, all it does is change 601 to "601" and I still get an error. (the example from my original post)

I'll try your second code.

 

[FormR]

I still get an error.

What is the error description?

The TextToDisplay argument is expecting a string - so the change from 601 to "601" is a step in the right direction.

 

[holt3130]

What is the error description?

The TextToDisplay argument is expecting a string - so the change from 601 to "601" is a step in the right direction.

the error was something like invalid procedure or argument. I don't remember. BUT your second code didn't kick out an error so I'm going to just go with that.

THANKS!