Code or formulas that calculate intermitent rows in a column...

[Acquaman]

Hello. Thank you for taking the time to read this. I very much appreciate it.

I have a some code that scans a spread sheet that contains a lot of empty rows and copies only the rows with data to a blank sheet in the workbook. This condenses the information making it more condusive for printing as a report. I have it inserting blank rows where the sums should be.

My problem is that I need a way to add formulas to the blank "spacer" rows between different items in the report that will calculate the sums of the rows of like items in the same column. Ideally, additonal sorting would not effect the formulas that do this, or code can recalculate the sums of the reorganized report.

Thank you in advance for any help you can provide.
Sincerely,
Acquaman

 

[earthandfire]

If you enter =SUM(A1:A5) in cell A6, you have the formula to sum the rows above in the current column. If you change the view so that you can see the formulae and also enable R1C1 you will see that the formula is actually =SUM(R[-5]C:R[-1]C).

When your code copies over the rows, you need to keep track of the number of columns and the number of rows above the row in which you want to enter the formula. Then in each formula cell enter the appropriate variation of the R1C1 formula.

Hope this helps.

 

[macropod]

Hi Acquaman,

You say you need to "calculate the sums of the rows of like items in the same column". If you're worksheet has a column that identifies these "like items", you might be able to use a SUMIF formula to aggregate them.

For example, say Column A has a number of consective rows of items similarly named items and the values you want to add are in Columns B-J on the next row. In that case, you could use the following line of vba to insert a suitable SUMIF formula on the next row:
ActiveSheet.Range(MyRange).FormulaR1C1 = "=SUMIF(C1,OFFSET(R1C1,ROW()-2,),C)"
where MyRange refers to the range of cells defined by your target row and Columns B-J.

You'll note that this avoids the need for the formula to include either the item names, the range of rows in which they exist or the columns to be summed.

Cheers

 

[Loomah]

It might be Friday but wouldn't Subtotals do the job?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​