Once you have that decision made, the writing of a macro-based solution becomes a whole lot simpler! There are lots of other options too.Ĭould I therefore respectfully suggest that you think a little more about how the data collection and presentation should work. In other words, mixing values in the same column could lead to potential difficulties.Īnother option when entering data is to interact with the spreadsheet user with something like an Input Box, where you prompt for Loads and Factors and have the macro write Tonnage and Factor values to the spreadsheet. What about your Column header (title)? Should it be "Loads", "Tonnage" or even "Loads/Tonnage"? After the data entry has finished, you would need to be able to tell which is a Load value and which is Tonnage, which might be difficult, especially without the 'T'. Perhaps you could insert the letter 'T' in the same cell as the tonnage value, but this will adversely affect deriving totals at the bottom of the column. So, how would you do that? You could use another cell in the same row as the calculation which is checked by the macro before it calculates the tonnage.
That's easy to do with a macro, but the danger is that the macro will recalculate the product of the two cells each time it is run, unless you have some form of indicator to show that the calculation has been performed and should not be repeated. You said that you would like the entered loads to be replaced by the calculated tonnage. I'm guessing the former, as you used the word "automatically". What you didn't specify is whether you want the macro to run every time, and as soon as, a value is entered, or if you want to manually run the macro at the click of a button. Similarly, you could have a macro place either the formula or the result into Column C. The Tonnage could be entered in Column C with a simple formula like =(A2*B2) and copied down the column. This is then summed and stuffed into its simplest form, you could have, for example, Loads in Column A and Factors in Column B. Note that a range (myRange) is defined as beginning with A1 and extending through whatever the End method returns. Range("B1") = WorksheetFunction.Sum(myRange) MyRange = ActiveSheet.Range("A1", Range("A1").End(xlDown))
The following code line shows how you can stuff the sum of the range starting at A1 and extending to just before the first blank cell in the column: To do this, it is best to rely upon the End method of the Range object.
What the macro doesn't do is to show how to select a variable number of cells to be summed. While this particular macro may not be that useful, it shows several helpful techniques, such as how to define a named range, how to use the SUM function, and how to stuff the sum into a cell. The results of the first run are put in B1, the second in B2, and the third in B3. It then uses the SUM worksheet function to assign the sum to the Results variable, which is (finally) stuffed into a cell in column B. Next loop to specify different ranges of cells to be summed. All you need to do is to preface the function name with "Application.WorksheetFunction." or simply "WorksheetFunction." Thus, if you know that each run of the macro will require summing A1:A100, then A1:A300, and finally A1:A25, you could use a macro like this: The problem is that the number of cells to be summed will vary for one run of the macro it could be 100 cells, while on the next it could be 300 and on the third only 25.įirst, it is easy to use most worksheet functions (such as SUM) from within a macro. Bob has a need to use the SUM function in a macro in order to find the sum of all the values in a column.