CODEWITHSUNDEEP
excelvbaxcodedefadvice
Techy
Techy

Reputation: 1

Displaying used formula in excel with Parameter names used in column A

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

Upvotes: 0

Views: 126

Answers (1)

Havard Kleven
Havard Kleven

Reputation: 421

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.

Upvotes: 0

Related Questions