Assign a variable to a range defined using the cells command

[kato01]

What I am trying to do is assign all cells in a range a hyperlink defined by the content of each cell in the range, i.e the hyperlink of A1 is the value of A1.

Public Sub Test()
Dim a1 As Range
Dim b1 As Range
Set a1 = Range(Sheets("sheet1").Cells(i, 2), Sheets("sheet1").Cells(i, 5))
For i = 1 To 1000
If InputRange.a1.Hyperlinks.Count > 0 Then
OutputRange.a1.Hyperlinks.Add Anchor:=a1, _
Address:=InputRange.a1.Hyperlinks(1).Address
End If
Next i
End Sub

I have a large number of cells populated with text that represent links.
For example:
a1 value is "www.msn.com"
a2 value is "www.google.com"
.
.
a10 value is "www.facebook.com"


I would like to assign to each cell a hyperlink to get something like:

sheet1.cells(1,1).Address:="http://www.msn.com" , TextToDisplay:="www.msn.com"
sheet1.cells(1,2).Address:="http://www.google.com" , TextToDisplay:="www.google.com"
.
.
.
sheet1.cells(1,2).Address:="http://www.facebook.com" , TextToDisplay:="www.facebook.com"

 

[DanteAmor]

Use the following

Public Sub Test()
    Set s1 = Sheets("sheet1")
    For i = 1 To s1.Range("A" & Rows.Count).End(xlUp).Row
        valor = s1.Cells(i, "A").Value
        s1.Hyperlinks.Add Anchor:=s1.Cells(i, "A"), Address:="http://" & valor & "/", TextToDisplay:=valor
    Next i
End Sub

Regards Dante Amor

 

[kato01]

Thank you

I am trying to keep away from using this definition system because I also have to deal with cells that are NOT in a specific order.

This is why I would like to be able to specify the row and the column of EACH CELL (i.e cells(i,j))

Use the following

Public Sub Test()
    Set s1 = Sheets("sheet1")
    For i = 1 To s1.Range("A" & Rows.Count).End(xlUp).Row
        valor = s1.Cells(i, "A").Value
        s1.Hyperlinks.Add Anchor:=s1.Cells(i, "A"), Address:="http://" & valor & "/", TextToDisplay:=valor
    Next i
End Sub

Regards Dante Amor

 

[DanteAmor]

Then:

Public Sub Test()
    Set s1 = Sheets("sheet1")
    Set a1 = s1.Range(s1.Cells(1, 1), s1.Cells(5, 1))   'from cell A1 to A5
    For Each celda In a1
        s1.Hyperlinks.Add Anchor:=celda, Address:="http://" & celda.Value & "/", TextToDisplay:=celda.Value
    Next
End Sub

Sintax: cell(i, j)

i represents the rows
j represents the columns

I explain to you
Your instrucción :

Set a1 = Range(Sheets("sheet1").Cells(i, 2), Sheets("sheet1").Cells(i, 5))

That is wrong, first because the i has no value, so it sends the error message: "Error defined by the application."
Second, you are putting from column 2 to column 5. The correct thing should be:

Range(cells(1, 1), cells(5, 1))

It means, that it goes from row 1 column 1 ("A") to row 5 column 1 ("A"), that is, from A1 to A5

Using your example:

a1 value is "www.msn.com"
a2 value is "www.google.com"
.
.
a10 value is "www.facebook.com"




I would like to assign to each cell a hyperlink to get something like:


sheet1.cells(1,"A").Address:="http://www.msn.com" , TextToDisplay:="www.msn.com"
sheet1.cells(2, "A").Address:="http://www.google.com" , TextToDisplay:="www.google.com"
.
.
sheet1.cells(10, "A").Address:="http://www.facebook.com" , TextToDisplay:="www.facebook.com"

You see the difference