Offset vba

[end922]

Greetings,

I am trying to add the bottom 3 cells in column C.
Like in C48, i need to know the sum of rows c47,46 and 45.
If I use =sum(offset(reference,rows,cols,[height],[width]))
it ask for a reference cell. I wont always know what that reference cell is. Should I be using a different formula?




Thanks
Eric

 

[SkipVought]

Hi,

1. Are you formatting a CELL FORMULA or are you just using the spreadsheet SUM function to return a value to your code?

2. Some code for the second option...

Function SumLastN(n As Integer)
    With ActiveCell
      SumLastN = Application.Sum(Range(Cells(.Row - n, .Column), Cells(.Row - 1, .Column)))
    End With
End Function

this is a user defined function. Paste in a MODULE, not a Sheet Object Code Window.

Use like any other ss function.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

For your reference cell, have a look at the 2 FAQs in this forum that deal with finding the last row of data on a spreadsheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question