Solved: Getting Hyperlink from cell

[SeanJ]

I need help with getting the hyperlink address from Column A and making a text value in Column C

[VBA]Option Explicit
Sub findALink()
Dim strcell As String
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To iLastRow
strcell = "C" + Trim(Str(i))
If Range(strcell).Value = "" Then

.Cells(i, "C").Value = .Cells(i, "A").Hyperlinks.Address

End If

Next i
End With
End Sub[/VBA]

Thanks

[mdmackillop]

I'm not clear of your code function, but here's the basic syntax
[vba] hAdd = ActiveSheet.Name & "!A" & i
.Cells(i, "C").Hyperlinks.Add Anchor:=.Cells(i, "C"), Address:="", SubAddress:= _
hAdd, TextToDisplay:=hAdd[/vba]

[SeanJ]

Sorry I was tired and in a rush. I have in Column "A" a word that is hyperlinked to somewhere on our intranet. So in column "C" if that cell is blank then take the hyperlink address of the cell in column "A" and add it the cell of column "C" as text only.

So if A3 cell is book1 with hyperlink of

http://www.cnn.com

So C3 should say this

http://www.cnn.com

Just text only no hyperlinked.

[Bob Phillips]

[vba]

Option Explicit
Sub findALink()
Dim strcell As String
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To iLastRow
strcell = "C" + Trim(Str(i))
If Range(strcell).Value = "" Then

On Error Resume Next
.Cells(i, "C").Value = .Cells(i, "A").Hyperlinks(1).Address
On Error Goto 0
End If
Next i
End With
End Sub
[/vba]

[shasur]

Here is a way to check the links

[VBA]Sub HLinks1()

For Each cell1 In Range("A:A").Hyperlinks
Range("C" & CStr(cell1.Range.Row)).Value = cell1.Range.Value
Next cell1

End Sub
[/VBA]

[SeanJ]

Thanks xld that did the trick. I going to run some test today with your code and other code that I have, and if all goes well I will mark this thread as solved after I finish this project.

[SeanJ]

Got it working and thanks everyone