MACRO question
I undertake a repetitive task, which seems to me is begging to be turned into a macro. The extent of my knowledge of macros is (a) start recording, (b) do some stuff, (c) stop recording. For most purposes this has been just fine. Now I want to do some more complex things and I've run into a problem. I have a column of numbers (say, for this example, A2, A3, A4). I wish to sum the column. I usually make the cell immediately to the right of the top number active (B2), and use autosum. Autosum is great, as it highlights the top of my column of numbers, and I can then use ctrl-shift-downarrow to automatically capture all my data. What this does is creates this formula: =SUM(A2:A4). I recorded my macro, doing just that, and it worked fine. But here is the problem. The number of rows in my data is variable from one day to the next. The next time I wish to do the calculation, my data might run from, say, A2, A3, A4, A5. However, the macro retains the formula =SUM(A2:A4), whereas is should be =SUM(A2:A5). How do I make the formula dynamic based on the number of rows? Ron |
MACRO question
|
MACRO question
OP wanted to start with A2, not a1
-- Don Guillett SalesAid Software "Kurt M. Sanger" wrote in message ... sum(a:a) will sum all the numbers in column A. "Don Guillett" wrote: try x=cells(rows.count,"a").end(xlup).row msgbox application.sum(range("a2:a" & x)) -- Don Guillett SalesAid Software wrote in message ... I undertake a repetitive task, which seems to me is begging to be turned into a macro. The extent of my knowledge of macros is (a) start recording, (b) do some stuff, (c) stop recording. For most purposes this has been just fine. Now I want to do some more complex things and I've run into a problem. I have a column of numbers (say, for this example, A2, A3, A4). I wish to sum the column. I usually make the cell immediately to the right of the top number active (B2), and use autosum. Autosum is great, as it highlights the top of my column of numbers, and I can then use ctrl-shift-downarrow to automatically capture all my data. What this does is creates this formula: =SUM(A2:A4). I recorded my macro, doing just that, and it worked fine. But here is the problem. The number of rows in my data is variable from one day to the next. The next time I wish to do the calculation, my data might run from, say, A2, A3, A4, A5. However, the macro retains the formula =SUM(A2:A4), whereas is should be =SUM(A2:A5). How do I make the formula dynamic based on the number of rows? Ron |
All times are GMT +1. The time now is 01:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com