Search code examples
excelvbadynamicsubtraction

VBA macro to subtract one variable number (dynamic) from a fixed number

I am after a dynamic macro that subtracts numbers in C2 onwards from B2 onwards and puts them in D2 onwards. Numbers in C2 onwards are fixed, although the user will put numbers in B2 onwards. The length of Column C (Hours Charged) changes dependent on the sheet that has been imported. The code is as follows:

Dim O As Long
O = Cells(Rows.Count, "C").End(xlUp).Row
Cells(O + 1, "C").Formula = "=SUM(C2:C" & O & ")"
Cells(O + 1, "C").Interior.Color = RGB(208, 247, 197)
Cells(O + 1, "C").Font.Bold = True

The sheet looks something like this (the first column is B, the last is D):

Hours Budgeted | Hours Charged | Variance
       0       |     85        |  0
       0       |     31        |  0
       0       |     20        |  0
       0       |     100       |  0
       0       |     75        |  0

To summarise, when a user inputs a value in the Hours Budgeted (B) column, i want it to automatically subtract from the Hours Charged (C) column and display in the Variance (D) column. The reason why this can't just be a '=C2-B2' is because I have a macro inputting all this data, as well as a separate macro that 'Resets' the sheet (removing all data and formulas).

Thanks all, Jake.

Solution

  • The following code should do the trick, just add it after the code you posted.

    For i = 2 To O
    Cells(i, "D").FormulaR1C1 = "=RC[-1]-RC[-2]"
Next i

    FormulaR1C1 lets you insert cells references that are relative to the cell you're putting the formula into, so R[2]C[3] would insert a reference to a cell three rows down and three columns to the right.