Hi
I'm still not quite sure I'm understanding your exact requirement tbh, as I'm having difficulty visualizing it...bear with me.
If you want the value in W9
1) set-up a macro to copy/paste W9 Range("O2") and fill down.
2) use a formula to show the text output of W9 i.e., =$W$9
If you want to show the formula in W9:
You can use: =Formulatext($W$9)
However, that only shows the formula as the value i.e., what you can see.
If you want to put the formula that is in W9 into column O, you can simply insert the formula using the macro.
Here below, you can see how I reference the dynamic table via the table name (LookUpTable) and the table header [POSTAL TOWN].
The index match formula contains named ranges but you can substitute yours into this so long as you reference the dynamic table (as I've shown beneath).
Using this method fills it down for you.
VBA Code:
Sub IndexMatch()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Worksheets("InputPostCodes").Activate
Range("LookUpTable[POSTAL TOWN]").Formula = "=INDEX(PostalTown,MATCH(PostCodeFirstHalfNew,PostCode,0))"
'Range("YourTableName[YOUR HEADER FOR COL O]").Formula = "=IFERROR(INDEX('Vendors with Addresses'!K:K,MATCH([@Payee],'Vendors with Addresses'!A:A,0)),"")
Range("LookUpTable[POSTAL TOWN]").Copy
Range("LookUpTable[POSTAL TOWN]").PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
Btw, named ranges are easy to use in a macro, that's the reason I started using them.
There are also ways to make named ranges via macros:
VBA Code:
Sub NameRange()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Worksheets("YourSheet").Activate
Range("YourTableName[YourHeaderName]").Select
ActiveWorkbook.Names.Add Name:="YourNamedRangeHere", RefersToR1C1:= _
"=YourTableName[YourHeaderName]"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
After stipulating this, you can then refer to a named range in a formula you insert via a macro.