VBA Copy Formula Down

Resolved · Urgent Priority · Version 2002/XP

Tue 10 Jan 2012, 12:27 Dav (guest) said...

VBA Copy Formula Down

I have a table of data in Excel. The range of the data in the table is B10:AJ15 (currently 5 rows of data). Cells AK10:AL15 are used to calculate totals based on the data in the table using a simple "Sum" formula.

I want to be able to automatically drag down the forumals in cells AK10:AL15 depending on how many rows of data have been added to the range B10:AJ15.

Is it possible to come up with a simple VBA statement which checks how many rows of data are present (starting from cell B10) and copies the formula's down in cells AK10:AL15 as required?

Wed 11 Jan 2012, 10:02 Trainer Anthony said...

RE: VBA Copy Formula Down

Hi Dav, thanks for your query. A simple VBA statement won't do the trick, I'm afraid. At the very least you'll need a number of them to declare variables and set up your loops and conditional tests. However, if you enter the formula below into AK16 and copy it down that column, it will calculate automatically when data is entered into the relevant cells.

=IF(AJ16<>"", SUM(B16:AJ16),"")

Hope this helps,

Anthony

Wed 18 Jan 2012: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Transpose text

You can transpose any range of cells, turning the columns into rows and the rows into columns. Just follow these steps:

Select the range.

Click the Copy button on the Standard toolbar to copy it to the Clipboard.

Select a cell outside of the range you copied.

Select Paste Special from the Edit menu.

In the Paste Special dialog box, click Transpose, then OK.

View all Excel hints and tips