CODEWITHSUNDEEP
vbaexcel
Michael
Michael

Reputation: 87

Use VBA to sum several cells above active cell

I need to use VBA to set a cell to sum several cells above the active cell. I have played around with some code that I came across but it is set to pull the entire column above the active cell. I do not understand the code enough to modify it to sum the cells two and three rows above. Here is what I have so far (only a portion of the macro posted):

Sub QRTLYdatagrab()

''''Script leading up to this point has been omitted''''

Range("U1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "SUM('Asset_Dtl for CEQs for FAS 157'!J:J)"
Range("U1").End(xlDown).Offset(2, 0).Select

Dim r As Range, rAbove As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Set r = ActiveCell
Set rAbove = Range(r.Offset(-2, 0), Cells(2, r.Column))
r.Formula = "=SUM(" & rAbove.Address & ")"

''''Script after this point has been omitted''''

End Sub

A quick example: one quarter my data may utilize 402 rows. I have summed rows 1 - 402 in 403. 404 is pulling a sum from another tab. I need 406 to sum 403 and 404. Please let me know if I need to clarify anything and thank you in advance for any help.

Upvotes: 1

Views: 3950

Answers (1)

Michael
Michael

Reputation: 87

This answer was provided by pnuts (site wont let me select his response as answer):

"Once you have selected the 406 cell 

Selection.FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"

– pnuts yesterday"

Here is how I have applied it to my own macro:

Range("U1").End(xlDown).Offset(3, 0).Select
Selection.FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"

This allows the macro to go to the end of Column U (where I have established two sum cells) then select the cell three rows down. This cell will then sum the cells 4 and 3 rows above.

Upvotes: 1

Related Questions