Solved: Extract hyperlink from cell and place in cell to right

[grichey]

Hello,

Is there a show command or anything for use with hyperlinks if I want to copy a hyperlink from from a cell and actually past the hyperlink in the next cell?

Example attached where first few cells are prior to macro and second set is what I'm trying to do.

[Simon Lloyd]

you can change the anchor cell.

[p45cal]

See attached which has small macro, and instructions on the sheet.Sub blah()
For Each cll In Selection.Cells
cll.Offset(, 1).Value = cll.Hyperlinks(1).Address
Next cll
End Sub

[grichey]

subscript out of range....

[p45cal]

Does it work in the attachment?
A bit more info on the error would help us help you.
You could type (after you've chosen to debug on the error, rather than stop the code):
?cll.Offset(, 1).Value
in the Immediate pane and see what it returns.
Do the same with:
?cll.Hyperlinks(1).Address

[grichey]

How does the immediate pane work? I've never used it for anything. I tried highlighting the entire column as well as highlighting the just a range of cells and got the same error.

[p45cal]

Does it work in the attachment?

[grichey]

nope

[grichey]

It's because of the spaces since there isnt a hyperlink in every cell in the whole column.

[grichey]

How does the hyperlinks syntax go? Rather, how would I check for something like the hyper link being non existant?

[grichey]

gonna mark solved on this and open a diff question re the syntax for future searchabiilty

[Simon Lloyd]

Using p45cal's code, skiping spaces:


Sub blah()
For Each cll In Selection.Cells
If cll.value=vbnullstring then goto Nxt
cll.Offset(, 1).Value = cll.Hyperlinks(1).Address
Nxt:
Next cll
End Sub

[p45cal]

or evenSub blah()
On Error Resume Next
For Each cll In Selection.Cells
cll.Offset(, 1).Value = cll.Hyperlinks(1).Address
Next cll
End Sub

[Simon Lloyd]

p45cal, i'm not keen on using On error resume next, i suppose its ok in a small application like that but due to its "Blind eye to everything" nature i very rarely use it. :)

[grichey]

nullstring is what I was looking for.

Awesome thanks

[grichey]

I have uploaded another example. This works with the spaces, but it does not get around the cells where there are not blanks where there is text but no hyperlink.

Suggestions?

[Simon Lloyd]

This should do what you need:Sub blah()
Dim cll As Range
For Each cll In Selection.Cells
MsgBox cll.Address
If cll.Value = vbNullString Or cll.Hyperlinks.Count = 0 Then GoTo Nxt
cll.Offset(, 1).Value = cll.Hyperlinks(1).Address
Nxt:
Next cll
End Sub

[grichey]

thanks hyperlinks count was the ticket.

[mdmackillop]

You can skip to GoTo by using Not

If Not (cll.Value = vbNullString Or cll.Hyperlinks.Count = 0) Then
cll.Offset(, 1).Value = cll.Hyperlinks(1).Address
End If