Excel custom hyperlink for each cell in range using VBA

Question

I have a module that removes all formulas from an entire sheet and then, it should, create a hyperlink formula on each cell using the cell value.

Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Test")

ws1.Range("B33:F533").Value = ws1.Range("B33:F533").Value


For Each i In ws1.Range("B33:B533")

i.Formula = "=HYPERLINK(""https://somelocation/" & i.Value & """,""" & i.Value & """)"

Next


End Sub

EDIT: It's now working perfectly. Thanks For all the help!

rotgers · Accepted Answer · 2015-10-26 11:27:28Z

5

i.Formula = "=HYPERLINK('https://somelocation/"&i.Value&","& i.Value"')"

Has an error in the formula. At the i.Value"') you are missing &, i.Value & "'. The correct one, that 'compiles', is added below:

i.Formula = "=HYPERLINK('https://somelocation/" & i.Value & "," & i.Value & "')"

Also it is worth noting that instead of writing "&i.Value&" and let the VBA IDE add the spaces, it is better to do it yourself, eg: " & i.Value & ".

You're code has 2 flaws, you should add the last & to get a syntax correct formula, and add spaces between the " and &, or else VBA won't see it is right.

Edit; a third flaw:

The line to create the formula is wrong too. Let's break it down: "=HYPERLINK('https://somelocation/" & i.Value & "," & i.Value & "')"

The parameter in the formula would be: 'https://somelocation/" & i.Value & "," & i.Value & "' so an output example of this line could be 'https://somelocation/1,1'. At first, I thought you are writing an URL with a comma, alright..? But then I looked at the HYPERLINK function in Excel and it requires two parameters. You are only giving one parameter. Excel formulas also expect a double quote instead of a single quote. You can escape a double quote in VBA like this "".

The correct line should be:

i.Cells(1, 1).Formula = "=HYPERLINK(""https://somelocation/" & i.Value & """,""" & i.Value & """)"

edited Oct 26, 2015 at 11:27

answered Oct 26, 2015 at 11:02

rotgers

2,0601 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1885099 Over a year ago

Your method did fix the compile error but now I get a "Run-time error '1004' Application-defined or object-defined error" on the same line

rotgers Over a year ago

You are using single quotes in the formula. It expects double quotes. You can escape a double quote in VBA by typing "". So this line should become "=HYPERLINK(""https://somelocation/" & i.Value & """,""" & i.Value & """)".

Collectives™ on Stack Overflow

Excel custom hyperlink for each cell in range using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related