Macro loop selecting different hyperlinks for different cells

Mattd213

New Member
Joined
Dec 6, 2017
Messages
1
Hello everyone.. I apologize if this comes off as a long-winded explanation..

At my job, we use Excel frequently for database management. One database in particular keeps information on individuals that are processed (Ex: Name, Reason Code, Date Processed, Case #, Ect...)

NameSSNDateReason CodeCase#CompanyApprover
John Doe00004/5/2012DA45678AtlanticJohn Smith
Jane Doe00015/6/2013SV123456BrockJohn Smith

<tbody>
</tbody>


Before a person's information is logged in the database spreadsheet, a case file must be made and submitted for approval. Once approved, The Case files are stored in a Hard Drive folder in PDF format. The person's information is then logged in the spreadsheet with the Case# in Column 5. Each Case# for each individual is hyperlinked to their respective PDF Case File.

My boss has recently assigned me the task of reviewing, completing, signing, and hyperlinking 5 years worth of case files that have been on the back burner since 2012. (400+ Case Files...) Over the past few days, I've neared its completion and have about 100 left to review and sign before the hyperlinking phase, in which I will have to check/hyperlink the 400+ case files to each individual in the database. This is where I need your help..

I have an idea for a VBA that will save me some time but my experience in VBA/Macros is minimal.. I've only just found out about this convenience and I'm attempting to absorb as much information as I can to learn it and use it to my benefit, but for now PLEASE HELP ME if you can.

So here's what I'm hoping for: I want a Macro that hyperlinks the PDF Case File to the Case# of each individual in the database automatically or through as few steps as possible. The PDF file name matches the value of Column A (the persons name).

Spreadsheet Example:
NameDateCase #Company
John Doe12/5/201245678Atlantic

<tbody>
</tbody>

PDF Example:
John Doe.pdf


These two values match and I'm assuming a find function exists that can match a cell value to a file name. I need to place the found file in another cell (in this case: the Case# portion) as a hyperlink. All of the PDF case files are in the same folder in the hard drive so the location stays the same, only the file name changes. What are my options or is this even doable?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
This is doable in VBA, but you can also do it using the HYPERLINK cell function if the hyperlink is going in a different column:
Code:
=HYPERLINK("C:\Users\AFPathfinder\Desktop\"&A2&".pdf",C2)

But if VBA is the request, something like this:
Code:
For i = 2 To finalRow
    Cells(i, 3).Value = "=HYPERLINK(" & """" & "C:\Users\AFPathfinder\Desktop\" & Cells(i, 1).Value & ".pdf" & """" & "," & Cells(i, 3).Value & ")"
Next i
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,600
Messages
5,625,741
Members
416,132
Latest member
Chandan Choubey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top