VBA/Microsoft Excel Mass Hyperlink Macro/Function

Suzann Solzhenitsyn 1 Reputation point
2022-10-27T14:27:27.5+00:00

I need help finding/creating a new macro function to a mass hyperlink, if possible. I have a spreadsheet with ~9000 lines. It is an invoice spreadsheet, which includes company names. I need to be able to, essentially, find and insert a hyperlink from the company name to a folder on Microsoft OneDrive. I can’t drag the hyperlink through the column because the company names are split up throughout the spreadsheet. I would individually do this, but I have 200 company names to hyperlink with thousands of contract numbers inside the invoice. I have a function created in Google Scripts, but I need to convert it to MS lingo. I either need help converting this function or someone who can create a better one to use.

Here is the Google Script function :

function hearCompanyName1(){ //function name
var range = SpreadsheetApp.getActiveSpreadsheet().getDataRange(); //calls range of sheet checks all cells
var values = range.getValues(); //gets all values

var found = values.map(function(row){
return row.reduce(function(acc, cell, ind) {
//Gets the string cells containing companyname1
if(typeof cell !== 'string' || cell.indexOf('CompanyName1') === -1){
acc[ind] = cell;
return acc;
}
acc[ind] = cell.replace('CompanyName1', '=HYPERLINK("https://COMPANYWEBBSITE.com", "CompanyName1")');
return acc;
}, []);
});
range.setValues(found);
}

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,735 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.