Macro to Hyperlink value in column A based on directory in column E and file name in column D

[willow1985]

Hi, I was hoping someone could help me with a macro that could hyperlink every cell in column A to the directory/file name in column D and F. I have a list of hundreds of employees and am looking for a quick way to link to their folders without doing it manually.

Thank you very much to anyone that can help!

Book1
	A	B	C	D	E	F	G	H	I	J
1	Employee #	Last Name	First Name	File Name	Directory					Hyperlink in column A
2	P2562	Medina	Dorian	Medina, Dorian	\\198.100.2.77\mydata\Employee Files\M\
3	P5551	Mata	Meredith	Mata, Meredith	\\198.100.2.77\mydata\Employee Files\M\
4	P1287	Pruitt	Juan	Pruitt, Juan	\\198.100.2.77\mydata\Employee Files\P\
5	P6524	Harrison	Annabel	Harrison, Annabel	\\198.100.2.77\mydata\Employee Files\H\
6	P0185	Copeland	Gary	Copeland, Gary	\\198.100.2.77\mydata\Employee Files\C\
7	P1830	Mcclure	Mckenna	Mcclure, Mckenna	\\198.100.2.77\mydata\Employee Files\M\
8	P8641	Hammond	Ben	Hammond, Ben	\\198.100.2.77\mydata\Employee Files\H\
9	P7135	Neal	Pedro	Neal, Pedro	\\198.100.2.77\mydata\Employee Files\N\
10	P0876	Macdonald	Moshe	Macdonald, Moshe	\\198.100.2.77\mydata\Employee Files\M\
11	P8626	Ellis	Alena	Ellis, Alena	\\198.100.2.77\mydata\Employee Files\E\
12	P4444	Parrish	Nathan	Parrish, Nathan	\\198.100.2.77\mydata\Employee Files\P\
13	P2384	Cross	Jaquan	Cross, Jaquan	\\198.100.2.77\mydata\Employee Files\C\
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=B2&", "&C2
E2:E13	E2	="\\198.100.2.77\mydata\Employee Files\"&LEFT(B2,1)&"\"

 

[PureBluff]

Hi,

I believe this is what you have requested....

Sub hyperl1nk()

Dim i, lastrow As Long

With ActiveSheet
lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

For i = 2 To lastrow
DirStr = Range("E" & i).Value & Range("D" & i).Value
ActiveSheet.Hyperlinks.Add Range("A" & i), DirStr
ActiveSheet.Range("I" & i).Value = DirStr

Next i

End Sub

 

[willow1985]

Hi,

I believe this is what you have requested....

Sub hyperl1nk()

Dim i, lastrow As Long

With ActiveSheet
lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

For i = 2 To lastrow
DirStr = Range("E" & i).Value & Range("D" & i).Value
ActiveSheet.Hyperlinks.Add Range("A" & i), DirStr
ActiveSheet.Range("I" & i).Value = DirStr

Next i

End Sub

This worked but it put data in column "I" (combined file name with directory)
If I changed how I have the sheet set up could we avoid this? (see below) I do not want to add any additional columns.

Thank you so much for your help!

hyperlink test.xlsm
	A	B	C	D	E	F	G
1	Employee #	Last Name	First Name	File Name	Directory		Hyperlink in column A
2	P2562	Medina	Dorian	Medina, Dorian	\\198.100.2.77\mydata\Employee Files\M\Medina, Dorian
3	P5551	Mata	Meredith	Mata, Meredith	\\198.100.2.77\mydata\Employee Files\M\Mata, Meredith
4	P1287	Pruitt	Juan	Pruitt, Juan	\\198.100.2.77\mydata\Employee Files\P\Pruitt, Juan
5	P6524	Harrison	Annabel	Harrison, Annabel	\\198.100.2.77\mydata\Employee Files\H\Harrison, Annabel
6	P0185	Copeland	Gary	Copeland, Gary	\\198.100.2.77\mydata\Employee Files\C\Copeland, Gary
7	P1830	Mcclure	Mckenna	Mcclure, Mckenna	\\198.100.2.77\mydata\Employee Files\M\Mcclure, Mckenna
8	P8641	Hammond	Ben	Hammond, Ben	\\198.100.2.77\mydata\Employee Files\H\Hammond, Ben
9	P7135	Neal	Pedro	Neal, Pedro	\\198.100.2.77\mydata\Employee Files\N\Neal, Pedro
10	P0876	Macdonald	Moshe	Macdonald, Moshe	\\198.100.2.77\mydata\Employee Files\M\Macdonald, Moshe
11	P8626	Ellis	Alena	Ellis, Alena	\\198.100.2.77\mydata\Employee Files\E\Ellis, Alena
12	P4444	Parrish	Nathan	Parrish, Nathan	\\198.100.2.77\mydata\Employee Files\P\Parrish, Nathan
13	P2384	Cross	Jaquan	Cross, Jaquan	\\198.100.2.77\mydata\Employee Files\C\Cross, Jaquan
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=B2&", "&C2
E2:E13	E2	="\\198.100.2.77\mydata\Employee Files\"&LEFT(B2,1)&"\"&B2&", "&C2

OR

hyperlink test.xlsm
	A	B	C	D	E	F
1	Employee #	Last Name	First Name	Directory		Hyperlink in column A
2	P2562	Medina	Dorian	\\198.100.2.77\mydata\Employee Files\M\Medina, Dorian
3	P5551	Mata	Meredith	\\198.100.2.77\mydata\Employee Files\M\Mata, Meredith
4	P1287	Pruitt	Juan	\\198.100.2.77\mydata\Employee Files\P\Pruitt, Juan
5	P6524	Harrison	Annabel	\\198.100.2.77\mydata\Employee Files\H\Harrison, Annabel
6	P0185	Copeland	Gary	\\198.100.2.77\mydata\Employee Files\C\Copeland, Gary
7	P1830	Mcclure	Mckenna	\\198.100.2.77\mydata\Employee Files\M\Mcclure, Mckenna
8	P8641	Hammond	Ben	\\198.100.2.77\mydata\Employee Files\H\Hammond, Ben
9	P7135	Neal	Pedro	\\198.100.2.77\mydata\Employee Files\N\Neal, Pedro
10	P0876	Macdonald	Moshe	\\198.100.2.77\mydata\Employee Files\M\Macdonald, Moshe
11	P8626	Ellis	Alena	\\198.100.2.77\mydata\Employee Files\E\Ellis, Alena
12	P4444	Parrish	Nathan	\\198.100.2.77\mydata\Employee Files\P\Parrish, Nathan
13	P2384	Cross	Jaquan	\\198.100.2.77\mydata\Employee Files\C\Cross, Jaquan
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	="\\198.100.2.77\mydata\Employee Files\"&LEFT(B2,1)&"\"&B2&", "&C2

 

[PureBluff]

Hi,
It wasn’t a helper column, you can remove this line:

ActiveSheet.Range("I" & i).Value = DirStr

to stop it.
I thought the yellow cell was a header, so I put data there (I instead of J though!)

You didn’t need to remodel the sheet

 

[willow1985]

Hi,
It wasn’t a helper column, you can remove this line:

ActiveSheet.Range("I" & i).Value = DirStr

to stop it.
I thought the yellow cell was a header, so I put data there (I instead of J though!)

You didn’t need to remodel the sheet

Perfect! Thank you very much!