I am using the following code to insert a formula into a cell using vba.
The code inserts a hyperlink with some static text leading to a file path and then at the end of my file path I want to be able to add a dynamic cell reference, for instance A and then the number of the row.
In my cell in column A I have the names of folders. I am using DestRow to define the current row number. So my question is how can I correct my formula so that when the link is clicked it opens the link to get the correct folder name of the row clicked? Thanks
ws2.Range("S" & DestRow).Formula = "=HYPERLINK(""\\UKSH000-FILE06\Purchasing\New_Supplier_Set_Ups_&_Audits\ATTACHMENTS\"" & K" & DestRow & ",""Attached"")"
Try,
ws2.Range("S" & DestRow).Formula = "=HYPERLINK(""\\UKSH000-FILE06\Purchasing\New_Supplier_Set_Ups_&_Audits\ATTACHMENTS\" & ws2.Range("K" & DestRow).Value & """,""Attached"")"
FWIW, I hate working with quoted strings as well.
Addendum: This should do for adding a static filename after the dynamic folder:
ws2.Range("S" & DestRow).Formula = "=HYPERLINK(""\\UKSH000-FILE06\Purchasing\New_Supplier_Set_Ups_&_Audits\ATTACHMENTS\" & ws2.Range("K" & DestRow).Value & "\audit.xls"",""Attached"")"
You could try including the INDIRECT() function:
ws2.Range("S" & DestRow).Formula = "=HYPERLINK(""\\UKSH000-FILE06\Purchasing\New_Supplier_Set_Ups_&_Audits\ATTACHMENTS\"" & INDIRECT(""K""&" & DestRow & ",""Attached"")"
