• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Top of Contiguous Range from Active Cell into Defined Formula Help

B

bkanne

Member
I'm trying to write some code that will select the top of a contiguous range relative to the actively selected cell, and use that in a formula. So far I have the following:

Code: 
  ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:OFFSET(RC,-1,0))"

In this example, I would like to replace the R[-4] component with a construct that will relatively select the top of the contiguous range above the active cell. For example, if there are 15 contiguous cells cells in the column above, the code would plug the top cell automatically instead of just going up 4 rows each time.

To be clear, I am purposefully using the OFFSET convention in the SUM formula. I would like to be able to automate the SUM shortcut of "Alt =" but instead of having a SUM Range, I would like to have a range that sums even if I add rows between the stack of data above the total

Not partial to R1C1 either...would prefer not to use it if possible.


Thank you for any help!
 

Attachments

  • Sample File_v1.xlsm
    14.9 KB · Views: 1
Hello bkanne

Welcome to Chandoo forum.

I was checking your file and it works so well with offset formula as and when you add rows with sum numbers...it is adding up..

I suggest no macro required...But still if you feel so...give me more information..

Please let us know...Happy to help you.
 
Yes, it works without the macro, but I am trying to create a macro that automates that sum/offset combination formula in the same way that using the "alt =" automatically sums a contiguous column of data above it.

The macro needs to identify the top portion of the column of data instead of rewriting the formula each time. This way when I am modeling I won't have to write that formula each time I want to sum a column a data using the sum/offset combination - the macro will do that for me.
 
Sorry bkanne

Still unable to get you...Why do you want Autosum...when you can do with Alt = with keyboard shortcut.

Let me know
 
Not sure what isn't clear.

There are disadvantages of using autosum in that if you add a row between the total and column of data, the range will not pick up the cell in the newly added row. If you use the offset method, it picks up the new row every single time. But I don't want to write the formula every time. Hence, I want a macro that writes it for me.

What doesn't make sense?
 
Anyone else have thoughts on how to achieve this? It seems like a relatively simple problem compared to some of the other items on here! Please let me know.

Thank you!
 
You must log in or register to reply here.
Back
Top