Forum Discussion
Solved
Dynamic subtraction macro showing the formula
I would like to create a dynamic subtraction macro which would display the formula.
Currently it is populating an error msg: "run time error 1004 application or object-defined error"
Below is my code so far:
Sub identify()
Dim bal As Long
Dim receipts As Long
Dim ws As Worksheet
Set ws = Sheet1
receipts = ws.Cells(ws.Rows.Count, "B").End(xlUp).row
For i = 12 To receipts
With ws.Range("D12:D")
.Formula = "=(B12:B & ""receipts"" & -C12:C & ""receipts"")"
.Value = .Value
End With
Next
End Sub
Appreciate the help in advance!
Sub subtract() Dim maxrow As Long maxrow = Tabelle1.Cells(Rows.Count, 2).End(xlUp).Row Range(Cells(12, 4), Cells(maxrow, 4)).FormulaR1C1 = "=RC[-2]-RC[-1]" End SubMaybe with this code. In the attached file you can click the button in cell F5 to run the macro.
5 Replies
Sub subtract() Dim maxrow As Long maxrow = Tabelle1.Cells(Rows.Count, 2).End(xlUp).Row Range(Cells(12, 4), Cells(maxrow, 4)).FormulaR1C1 = "=RC[-2]-RC[-1]" End SubMaybe with this code. In the attached file you can click the button in cell F5 to run the macro.
OliverScheurich , if i would like to carry out a dynamic sum from a specific row to another row, how should the code be written in formular1c1 method?
this is my code so far..
receipts = ws.Cells(ws.Rows.Count, "B").End(xlUp).row receipts.Offset(1).Forumlar1c1 = "=sum(R[receipts]C:R[-1]C)"Sub sum() Dim receipts As Long With Tabelle1 receipts = Cells(Rows.Count, 1).End(xlUp).Row Cells(receipts, 1).Offset(1, 1).FormulaR1C1 = "=sum(R12C2:R[-1]C)" End With End SubMaybe with this code. In the attached file you can click the button in cell D4 to run the macro.
- Try Set ws = Sheets("Sheet1")
