How to write a smart macro which know how many cells to include in the calculation?
I need a macro to calculate the sum of some data. Problem is the amount of data is diffrent for each month. For example: I may have 10 sales data this month in cell A2 to A11 and next time I will have 20 data in cells from A10 to A30. How to write a macro which will give me the total sales every month, automatically know how many cells should be used for the calculation? I would like to have the total amount shown in the cell 2 rows below the last data cell. (A13 for this month and A27 for next month in the example above).
I will appreciate it if you could tell me step by step how to record such a macro. If not, a code will do it. I know a offset command will be needed but did not figure out how to do it. Thanks!
- 1 decade agoFavorite Answer
I will give you some hints.
You need to come up with an algorithm for the VBA code to recognize when the last row is reached.
One option is to always a blank cell after the last entry; then a For Next loop could look for the blank cell and exit when it is reached. The total would then be entered after the blank cell.
An alternate algorithm is to use Ranges. In other words, the range would expand to fit the number of data elements. This way the VBA code would only need to focus on the data within the range.
This is your first step. Once you decide that, it will be easier to write the VBA code that you need.
I don't know how much experience you have in VBA: this would determine how much details I would need to provide. By the way, you cannot record a macro that would accomplish what you need, you would need to write the code yourself. The reason is that you need FOR NEXT loop or IF THEN statements. The macro recorder does not have this capabilitySource(s): 3 years of VBA experience.
- goveLv 44 years ago
I conform to colanth try one extra element elect the selection the position values are kept replace (press Ctrl+H) in locate what field variety Comma " , " and replace with do not enter some thing (some thing is already there dirt delete it) press replace all back do the same element for replacing the only quote " ' ". now try the sum this time also if the finished isn't superb perfect then take the cost in new colum with the help of this function and take a verify out taking the sum of that =cost(sparkling(manage of the cellular))