Solved: How to get a URL address from the cell value

[khalid79m]

Cell A3 VALUE = 4422259
Cell A3 Link = http://cm-web.systems.uk./E!/Report/...spx?id=4422259

in a4 i want to show the link address as above how can i get that using vba ? or excel ?


thanks in advance for look at this post

[Bob Phillips]

[vba]

With Range("A3")

.Offset(1, 0).Value = .Hyperlinks(1).Address
End With
[/vba]

[Oorang]

[VBA]Sub GetHyperLink()
Dim rng As Excel.Range
Dim hlk As Excel.Hyperlink
Dim strMsg As String
Set rng = Excel.Selection
For Each hlk In rng.Hyperlinks
strMsg = strMsg & (hlk.Range.Address & vbTab & hlk.Address & vbLf)
Next
MsgBox strMsg
End Sub[/VBA]

[khalid79m]

 
With Range("A3") 
     
    .Offset(1, 0).Value = .Hyperlinks(1).Address 
End With 

this changes a3 to the addres. I want to show the address in a4 and not change anything for a3 can you help

[Bob Phillips]

[vba]

With Range("A4")

.Offset(-1, 0).Value = .Hyperlinks(1).Address
End With
[/vba]

[khalid79m]

With Range("J2:J" & lastrow)
.Offset(-8, 0).Value = .Hyperlinks(1).Address
End With

this doesnt work...

how can i change the .OFFSET(-8,0) TO $A2

[Charlize]

[VBA]Sub adresses()
Dim myrange As Range
For Each myrange In Range("J1:J" & lastrow)
With myrange
.Offset(0, -9).Value = .Hyperlinks(1).Address
End With
Next myrange
End Sub[/VBA]Charlize

[khalid79m]

worked great