CODEWITHSUNDEEP
excelvbaexcel-formula
Adam
Adam

Reputation: 83

VBA to add hyperlink function to each cell in a column

Hello and thank you in advance for your help. Currently I created the Macro below that updates the range with a hyperlink function. This works great. However, the issue is that this data gets refreshed via a datasource. Once refreshed there are blanks in Column A. Is there a way to execute this when a refreshed datasource occurs? 

Sub InsertHyperlinkFormulaInCell()

ActiveWorkbook.Worksheets("Query").Range("A2:A20000").Formula = 
"=HYPERLINK(CONCAT(x2,B2),W2)"

End Sub

Any help would be greatly appreciated. Thank you!

enter image description here

Upvotes: 0

Views: 2540

Answers (1)

fixnomal
fixnomal

Reputation: 105

you need to loop through the rows to change the hyperlink in every row otherwise you're just changing row 1. If you want to keep the Excel reference in the sheet you can just exchange the row number in a for loop (or in this case while loop) which is sometimes nice to have but it makes for clunky code in the concatenation process.

Sub InsertHyperlinkFormulaInCell()

    'starting with row 2 since row 1 is title row
    currentRow = 2

    ' cells allows to call columns by number instead of letters (so A becomes 1, B:2 etc.)
    While Cells(currentRow, 2) <> "" 'check whether Column B is empty, stop if it is
        'This is your active line, just replaced the row number 1 with the variable currentRow
        'which loops through the sheet until it encounters and empty cell in column B
        ActiveWorkbook.Worksheets("Sheet1").Cells(currentRow, 1) = "=HYPERLINK(CONCAT(B" & currentRow & ",C" & currentRow & "),D" & currentRow & ")"
        currentRow = currentRow + 1
    Wend

End Sub

Hope that helps

Upvotes: 1

Related Questions