Excel VBA

phillipsdj

Hi all,

I have created a macro for my new team at work which automates a lot of manual work they have been needlessly doing for years in Excel. But, sadly, my VBA skills are not as good as I would like them to be and I have hit a deadend.....

Could anyone tell me if it's possible to add text and the sum formula at the bottom of multiple dynamic ranges?

For context, my macro currently pastes data into 12 worksheets (months) and each sheet has between 3 or 5 ranges. At the bottom of each range I would like to add "Total" in column N and the Sum formula in cells O and P.

Thanks in advance for any help.

PhilCo_2

The simplest answer is to have them at the top so that they just look at data in the table below. Works better that way if you want to link a summary sheet to the data as well. But if you want them at the bottom of the table you can use something like this to get to the row below the last one with data in..

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Which will basically go down column A to the last filled cell. The Offset bit will move 1 cell below. To move 1 cell down and to the right you would use (1, 1). You can then use..

ActiveCell.Formula =

to enter the data text/formula you want.

TrickyDicky101

How are the ranges on each sheet identified? ie are they named ranges or do they have a specific marker that could be used as reference on the sheet (ie each range has the phrase 'Total' in the last row)? Given that these ranges are dynamic (and thus may be of 1 to many rows in length) you need some way of identifying the last row in each range. Potentially this could be identified if there are entirely blank rows between the ranges. Please provide more detail.

Incidentally, given your exact circumstances, it is likely that FormulaR1C1 will prove more useful for entering the formulas - but again, it would help if you could explain how the ranges can be identified.