Search code examples
vbaexcelformulacopy-paste

VBA / Excel : Possibility to copy formula dynamically

I want to copy a formular from one Cell to another in the same row with VBA,

e.g.

E1 = "=(E441+E747+E750)*-1"
to
  F1 -> = "=(F441+F747+F750)*-1"

But this code snippet:

Dim r As Excel.Range
Dim lRow As Long


lRow = tbXlApp.WorksheetFunction.Match("Test", Range("C:C"), 0)

'Line 4
tbXlWs.Cells(lRow, 5).Formula = "=(E441+E747+E750)*-1"

For Each r In tbXlWs.Range(tbXlWs.Cells(lRow, 6), tbXlWs.Cells(lRow, 4 + x))
    r.Formula = tbXlWs.Cells(lRow, 5).Formula
Next r

Simply pastes the formula of the cell defined in Line 4.

Recalling the example, it results in

   E1 = "=(E441+E747+E750)*-1"
   to
     F1 -> = "=(E441+E747+E750)*-1

But it should be Column F in this scenario. What do I need to change?

Thanks!

Solution

  • You could set the formulas at one time:

    Dim lRow As Long

lRow = tbXlApp.WorksheetFunction.Match("Test", Range("C:C"), 0)

'Line 4
tbXlWs.Range(tbXlWs.Cells(lRow, 5), tbXlWs.Cells(lRow, 4 + x)).Formula = "=(E441+E747+E750)*-1"

    Each formula is then adjusted based on the relative columns, just as if you copied it within Excel.