I'm working on a database that has a number of equations embedded in the sheet that we ideally want to move to the "backend" VBA code. I want the equations to update whenever their dependents are changed, so far this is how I've built it (for an equation with three dependents)
If Not ((Intersect(Target, Range("AJ:AJ")) Is Nothing) Or (Intersect(Target, Range("AJ:AJ")) Is Nothing) Or (Intersect(Target, Range("AJ:AJ")) Is Nothing)) Then
Row = Target.Row
Cells(Row, Application.Match("val1", Range("1:1"), 0)).Value = (Cells(Row, Application.Match("val2", Range("1:1"), 0)).Value + Cells(Row, Application.Match("val3", Range("1:1"), 0)).Value) * Cells(Row, Application.Match("val3", Range("1:1"), 0)).Value
End If
It's working of headers as columns are being moved around still, I could define each Application.Match as a variable higher up but that would add more lines. Is there anyway I could make this easier to read/run more efficiently? Thanks. EDIT: to clarify this is being moved to vba back as managers are worried that people will overwrite equations in the cells and cause problems from this.