Forum Discussion
Using VBA to have a cell equal a sum of cells in another worksheet
Hello, I'm trying to create a vba macro that allows me to clear the current value of cells in one spreadsheet and then have the cleared cell be set to the sum of a range of cells in another sheet. I'...
rec and pay are Range objects. You have to use the keyword Set to assign them:
''Set Accounts Receivable to 0 while keeping sum of new sales
Dim rec As Range
With Worksheets("Invoice")
Set rec = .Range(.Range("E2").End(xlDown).Offset(1, 0), .Range("E2").End(xlDown).Offset(10, 1))
End With
Worksheets("BS & IS").Range("C6") = WorksheetFunction.Sum(rec)
'Set Accounts Payable to 0 while keeping sum of new purchases
Dim pay As Range
With Worksheets("Purchase Orders")
Set pay = .Range(.Range("F2").End(xlDown).Offset(1, 0), .Range("F2").End(xlDown).Offset(10, 1))
End With
Worksheets("BS & IS").Range("F5") = WorksheetFunction.Sum(pay)WorksheetFunction.Sum expects numbers and/or ranges as argument, not a text string, so WorksheetFunction.Sum("...") won't do what you want.
Thank you! The code is working and setting the value of the cells to 0 as it should. However, is it possible to set the cells formula as =SUM(" : ") for the range in the Invoices and Purchase Orders sheets such that the cells in BS & IS update with new entries? Would I have to do
Worksheets("BS & IS").Range("C6").Formula = ...in order for this to happen?
Yes. In that case, don't use WorksheetFormula, but set the formula of those two cells.