After the In, we set the main object we are parsing TargetCells. Then you want to create a variable for the subset, in this case, subRow. Next, you are going to start the loop by using For Each. Add one to it to move it to the right of your data by appending +1. You set it equal to the Count variable of the Cells class of AllCells. You use the ColumnPlaceHolder variable to set this target. The details are virtually identical.īefore you start the loop for each row, you need to set the target column where the loop writes the average of each row.
EXCEL 2011 MAC VBA CODE
Since they are almost exactly the same, only one of them is here but both are in the code block. In this case, you are doing two of them, one for each row and one for each column. These loops go through an object to act on each subset of that object.
The next two sections of code are For Each loops. Set TargetCells = Range(AllCells.Cells( 2, 2), AllCells.SpecialCells(xlCellTypeLastCell)) You can see both of these in the code block below. This time using SpecialCells method to get the property xlCellTypeLastCell. To get the final cell in the range, you will still call AllCells. You call this by calling your AllCells range, using its Cells class to get that specific cell using (2,2). Its start address is going to be the cell at the second row in the second column of the range. Instead, you'll use a subset of the AllCells range. The problem is you don't want the labels included in the average and sum data. You get this by calling the ActiveSheet object and then it's UsedRange property. The variable All Cells will be set to all the active cells on the sheet, which includes the column and row labels. Ranges are objects that hold sections of the worksheet as addresses. Now that you have all of your variables, you need to use some of the range variables right away. You should declare all variables using Dim before the name, and then as with the datatype. These are in the code block below, but a note about how they are constructed. To begin, you will need to declare all of your variables. Your macro should be highlighted, click run to add your sums and averages. Once you have another sheet of data, go back to Developer and click Macros. Your macro is now able to use on each new sheet you add to your workbook. Then, paste that into the cells in rest of the column. Then in the next cell down, enter =Average(B2:F2). Then in the header add Average after the last column. Then copy and paste that into the rest of the columns. In the cell next to it, enter =SUM(B2:B10).
Click okay to start setting up the macro.Īt the bottom of the hourly listings enter Daily Totals. You can enter a description if you need more details on what the macro does. In the dialog enter the name as AverageandSum and leave it stored in This Workbook. (If you don't have data to populate this sheet, you can enter =RandBetween(10,1000) in all the cells to create dummy data.) Next, click on Developer in the Ribbon. Then fill out your sales data for the day. Add a new tab, and copy your template into it.