Macro to create HYPERLINK referencing cell to the left

[AjNau]

I've createed a macro that will insert a new column to the right of the active cell and create a formula in the new column using the HYPERLINK function.

My existing macro works if the spreadsheet columns remain constant, but how can I make the macro more robust so it can work in other spreadsheets with a different column layout?

For example...

ActiveCell is A2

A2 = "0042902"

...the macro creates the following formula in B2, a new column.

B2 = HYPERLINK("http://someurl/query.asp?"&A2,A2)

How can I modify the macro for when ActiveCell is A5 and is "0042904"?

Current code...

Sub HYPLink()
    ActiveCell.Offset(0, 1).Range("Table2[[#Headers],[number]]").Select
    Selection.ListObject.ListColumns.Add Position:=2
    ActiveCell.FormulaR1C1 = _
        "=HYPERLINK(""https://someurl/query.asp?number=""&[@number],[@number])"
End Sub

I have been trying to use the ActiveCell.Address in place of [@number], as the data is not always in a table, nor is it always in column 1 (A).

    Dim MyRef As String
    MsgBox ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    Set MyRef = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    ActiveCell.FormulaR1C1 = _
        "=HYPERLINK(""https://someurl/query.asp?number=""&MyRef,MyRef)"

However setting MyRef returns an error : "Object required".

I don't understand why this error is occurring. Other posts suggest it should work. What silly mistake am I making?

Or is there an easier way to acheive my original goal?

Thanks, AjNau

 

[AjNau]

Bump

 

[AjNau]

I haven't been able to solve this???