Excel VBA Insert Hyperlink into Cells

Asked Jan 24 '22 at 13:24

Active Jan 24 '22 at 13:24

Viewed 14 times

I am trying to insert following hyperlink formula into excel cell:

=HYPERLINK("#"&RIGHT(FORMULATEXT(S1);LEN(FORMULATEXT(S1))-1);"Ex-"&P1&" #"&R1&"/"&S1&", "&TEXT(Q1;"dd.mm.yyyy"))

I am trying to do it by following excel VBA code:

Link = "=HYPERLINK(""#""&RIGHT(FORMULATEXT(S" & RowIndexTemp & ");LEN(FORMULATEXT(S" & RowIndexTemp & "))-1);""Ex-""&P" & RowIndexTemp & "&"" #""&R" & RowIndexTemp & "&""/""&S" & RowIndexTemp & "&"", ""&TEXT(Q" & RowIndexTemp & ";""dd.mm.yyyy""))"
ThisWorkbook.Worksheets(Sheet).Cells(RowIndex, ColIndex).Formula = Link

but the result is "Run-time error 1004 Application-defined or object-defined error" on last row (ThisWorkbook... = Link) Where I do error? (I know Add.Hyperlink function but I would like to do it this way.) Thank you for your helping.

asked Jan 24 '22 at 13:24

Fidzi

Assuming the formula is fine, you need to change the `;` to `,`, or use `.FormulaLocal` instead of `.Formula`. – BigBen Jan 24 '22 at 13:28
1

Thank you very much. It is working for 100%. – Fidzi Jan 24 '22 at 13:50

Excel VBA Insert Hyperlink into Cells

0 Answers0