using VBA to populate a hyperlink field

[Paul]

I have a spreadsheet that contains a column of six-digit numbers, and I
would like to use VBA to create a second column of hyperlinks using the
values in the first column. For example, if the first three records in the
first column consist of the following numbers,

124879
126978
198790

I would like to use VBA to populate the second column where the hyperlink
"Text to Display" would be the six digit number, and the hyperlink "Address"
would be a concatenation of a constant string and that number:

"M:\Projects\" & 124879 & "\"
"M:\Projects\" & 126978 & "\"
"M:\Projects\" & 198790 & "\"

I realize that if I were simply trying to copy a modified version of one
column into another I would use something along the lines of:

For k = 1 to n
Worksheets("Sheet1").Cells(k, 1).Copy
Worksheets("Sheet1").Cells(k, 2).PasteSpecial Paste:=xlValues
Next k

But in this case, I'm trying to create a column of hyperlinks that require
two different values for each cell - the "Text to Display" and the
"Address."

How can I use VBA to create this column of hyperlinks?

Thanks in advance,

Paul

 

[Tim Williams]

I know you asked for a VBA solution, but have you tried using

=HYPERLINK([address],[text to display])

=HYPERLINK("M:\Projects\" & 124879 & "\","Open folder")

If you want to use VBA, you could just insert that formula, or create the
link directly.

Sub Macro1()
Dim c As Range

For Each c In ActiveSheet.Range("A1:A100").Cells
If c.Value <> "" Then
c.Parent.Hyperlinks.Add Anchor:=c.Offset(0, 1), _
Address:="M:\Projects\" & c.Value & "\", _
TextToDisplay:="Open folder"

End If
Next c

End Sub



Tim

 

[Paul]

That's what I was looking for, and I'll try out both suggestions, Tim.

Thanks so much for your help.

Paul

I know you asked for a VBA solution, but have you tried using

=HYPERLINK([address],[text to display])

=HYPERLINK("M:\Projects\" & 124879 & "\","Open folder")

If you want to use VBA, you could just insert that formula, or create the
link directly.

Sub Macro1()
Dim c As Range

For Each c In ActiveSheet.Range("A1:A100").Cells
If c.Value <> "" Then
c.Parent.Hyperlinks.Add Anchor:=c.Offset(0, 1), _
Address:="M:\Projects\" & c.Value & "\", _
TextToDisplay:="Open folder"

End If
Next c

End Sub



Tim

I have a spreadsheet that contains a column of six-digit numbers, and I
would like to use VBA to create a second column of hyperlinks using the
values in the first column. For example, if the first three records in
the first column consist of the following numbers,

124879
126978
198790

I would like to use VBA to populate the second column where the hyperlink
"Text to Display" would be the six digit number, and the hyperlink
"Address" would be a concatenation of a constant string and that number:

"M:\Projects\" & 124879 & "\"
"M:\Projects\" & 126978 & "\"
"M:\Projects\" & 198790 & "\"

I realize that if I were simply trying to copy a modified version of one
column into another I would use something along the lines of:

For k = 1 to n
Worksheets("Sheet1").Cells(k, 1).Copy
Worksheets("Sheet1").Cells(k, 2).PasteSpecial Paste:=xlValues
Next k

But in this case, I'm trying to create a column of hyperlinks that
require two different values for each cell - the "Text to Display" and
the "Address."

How can I use VBA to create this column of hyperlinks?

Thanks in advance,

Paul