I need your expert advice to provide a VBA script that automatically displays used formula in excel. For example
The cell D4 should display the formula used in B4 with the Parameter names used in column A.
The script below in VBA provides the formula with cell value! But I need with reference to Parameter name mentioned in column A.
Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function
CodePudding user response：
As long as your function stays in the same place, you could do something like this:
Public Function GetFormula(cell As Range) As String
V_in = cell.Offset(-2, -1).Value 'Replace(cell.Address, "$", "")
V_in_adr = cell.Offset(-2, -1).Address
R_load = cell.Offset(-1, -1).Value 'Replace(cell.Address, "$", "")
R_load_adr = cell.Offset(-1, -1).Address
opr = Mid(cell.Formula, Len(V_in_adr), 1)
GetFormula = "=" & V_in & opr & R_load
End Function
The function retrieves the operator used in the formula, and puts it in the middle of the value in each cell (V_in and R_load). Could be further imporved by using offset values as input to the formula, however this works for your current worksheet.
CodePudding user response：
Mr.Kleven, if I add a different formula it shows incomplete result with existing script. Shared the snapshot just for your reference. Just incase if you already have a solution to address this issue!
