Creating dynamic Hyper Link

[dgkindy]

I am trying to dynamically create a hyperlink based off a list of street names.

The problem occurs when I try to add the hyperlink to the spreadsheet.

I get the following error message

run time error 5

Invalid procedure or call argument

Sub Hyper()    
    Dim x As Integer
    x = 2
    Sheets("Streets").Select
    Do While Cells(x, 2) <> ""
        Cells(x, 2).Select
        Hyperaddress = "http://www.canada411.ca/search/ad/1/-/cZQQstZ" & Cells(x, 2) & "QQciZ" & Cells(1, 2) & "QQpvZONQQpcZ/?pgLen=10000"
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Hyperaddress, TextToDisplay:=Cells(x, 2)
        x = x + 1
    Loop
End Sub

 

[Roderick_E]

At first glance try naming selection
linker = Cells(x, 2).Select

then use the name in the hyperlink part
Hyperaddress = "http://www.canada411.ca/search/ad/1/-/cZQQstZ" & Cells(x, 2) & "QQciZ" & Cells(1, 2) & "QQpvZONQQpcZ/?pgLen=10000"
ActiveSheet.Hyperlinks.Add Anchor:=linker, Address:=Hyperaddress, TextToDisplay:=Cells(x, 2)
x = x + 1

 

[dgkindy]

Thanks but that did not appear to fix the problem.

I know get a Type mismatch error.

 

[Roderick_E]

might need to DIM linker as value or string or whatever it is.

 

[dgkindy]

Still no joy. I don't think the DIM would be the problem as VBA is pretty flexible in that you don't have you declare a lot of the basic variable to get the code to work.

Sub Hyper()    Dim x As Integer
    Dim linker As String
    x = 2
    Sheets("Streets").Select
    Do While Cells(x, 2) <> ""
        linker = "Cells(x, 2).Select"
        Hyperaddress = "http://www.canada411.ca/search/ad/1/-/cZQQstZ" & Cells(x, 2) & "QQciZ" & Cells(1, 2) & "QQpvZONQQpcZ/?pgLen=10000"
        ActiveSheet.Hyperlinks.Add Anchor:=linker, Address:=Hyperaddress, TextToDisplay:=Cells(x, 2)
        x = x + 1
    Loop
End Sub

 

[dgkindy]

So the problem ended up being the Display name. You were close Roderick. Working code shown below,

Sub Hyper()    Dim x As Integer
    x = 2
    Sheets("Streets").Select
    Do While Cells(x, 2) <> ""
        Cells(x, 2).Select
        Display = Cells(x, 2)
        Hyperaddress = "http://www.canada411.ca/search/ad/1/-/cZQQstZ" & Cells(x, 2) & "QQciZ" & Cells(1, 2) & "QQpvZONQQpcZ/?pgLen=10000"
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Hyperaddress, TextToDisplay:=Display
        x = x + 1
    Loop
End Sub