Macro to add hyperlink with variables

[jtf]

Related to my macro in this thread...
I got everything working thanks to the help in that thread, except for the column to add hyperlinks. The link needs to have a variable which is found in column A.

If I were to type the formula, it would look like this:

=HYPERLINK("https://npapps.mywebsite.com/DsmWeb/do/application/sbs/customer/View?applicationId="&A2&"",A2)

But this fails in a macro because of the quotes.
I was given this as an option in the previous thread:

=HYPERLINK("https://npapps.mywebsite.com/DsmWeb/do/application/sbs/customer/View?applicationId="&Range("A2") & "," & Range("A2")&")"

But it returns this error message:
Compile error - expected list separator or )

Any suggestions?

Thanks,
Jeff (Win7, Excel 2007)

 

[doubledaffy]

Do you need the quotes in the url? If so, you'll probably have to use chr(34). Other than that, the syntax is just slightly off, missing a parenthesis, comma or ampersand somewhere.

 

[jtf]

Do you need the quotes in the url? If so, you'll probably have to use chr(34). Other than that, the syntax is just slightly off, missing a parenthesis, comma or ampersand somewhere.

No, the quotes are not part of the URL. They are there to tell excel to include the number in cell A2 as part of the URL.

 

[doubledaffy]

Whoever gave you the code was just including the parenthesis as part of the url text, so there is no close for the HYPERLINK code.

This should work


=HYPERLINK("https://npapps.mywebsite.com/DsmWeb/do/application/sbs/customer/View?applicationId=" & Range("A2") & "," & Range("A2"))</pre>

 

[jtf]

Hmmm, trying that and I still get an error:
Compile Error: Expected End of Statement

and it highlights the https

I think it needs another quote, but that still doesn't work. I added the double quotes at the end (tried with a space and w/o) and it still gives the error.

..... " & Range("A2") & ""," & Range("A2"))

 

[jtf]

I got it. I used the "record macro" function and then copied what it created. The correct code is this:
"=HYPERLINK(""https://npapps.mywebsite.com/DsmWeb/do/application/sbs/customer/View?applicationId=""&R[0]C[-29]&"""",R[0]C[-29])"

Thanks for the help

 

[doubledaffy]

Glad that works. I was going to suggest this code:

"=HYPERLINK(" & Chr(34) & "https://mywebsite.com/DsmWeb/do/application/sbs/customer/View?applicationId=" & Range("A2") & "," & Range("A2") & Chr(34)

The problem was just confusion with parenthesis and quotes because there were so many of them embedded in the string.