0

I need your expert advice to provide a VBA script that automatically displays used formula in excel. For example

enter image description here

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
braX
  • 11,506
  • 5
  • 20
  • 33
Techy
  • 1

1 Answers1

0

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.

Havard Kleven
  • 422
  • 6
  • 19