98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA Copy Formula Down
VBA Copy Formula Down
Resolved · Urgent Priority · Version 2002/XP
Tue 10 Jan 2012, 12:27Dav (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:02Trainer 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 information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Transpose textYou can transpose any range of cells, turning the columns into rows and the rows into columns. Just follow these steps: |
