Is there a way to edit a formula in vba

Question

Hypothetically if I had a formula in a cell and I wanted to edit that formula how would I go about it?

The example I am looking for is I want to add $ signs at certain points within the formula, is there a way to tell VBA to add $'s after certain characters within a formula?

I am not looking for a method to turn the formula into an absolute reference formula, I just want to know how to add characters or symbols at certain points within the formula

The formula's example: This is what is pasted into the cell by VBA

=IF(A13="Please add a title",0,B17*VLOOKUP(A13,'Tables (H)'!$H$2:$J$6,2,FALSE))

After editing I want it to look like this

=IF($A$13="Please add a title",0,B17*VLOOKUP($A$13,'Tables (H)'!$H$2:$J$6,2,FALSE))

Answer 1

This is a slightly different approach than finding the position. If, for example, the formula is in a single-cell range called Cell you can use Replace:

Cell.Formula = Replace(Cell.Formula, "A13", "$A$13")

EDIT:

Okay, here's something that uses the first cell of a table/ListObject in a formula. That may be a more direct way to what you want. If not, I think you can alter it to use the first cell of a named range:

Sub test()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim FirstTableCell As Excel.Range
Dim CellWithFormula As Excel.Range

Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
Set FirstTableCell = lo.DataBodyRange.Cells(1)
Set CellWithFormula = ws.Range("A2")
CellWithFormula.Formula = "=" & FirstTableCell.Address & "+1000"

End Sub