VBA Macro to sum a variable range and put the result in the specified column

Question

Hello,

I am creating a macro that would calculate the sum from the column(TOTAL_DLR_AM) of the table(calculation) and paste the calculated sum in the next table(Approvals Summary) in the column(Sum).

However i try but i am only able to calculate the sum but not able to paste the calculated sum in the column(Sum).

I am not using the range select because the data in the table (calculation) will very.

Below is the code:

Sub Macro()

    Windows("techdata.xlsm").Activate

    Sheet1.Activate

    ActiveCell.FormulaR1C1 = "Sum"

    ActiveCell.Offset(0, 1).Select

    myrow = Range("E6666").End(xlUp).Row

    ActiveCell.Offset(0, 4).Formula = "=SUM(E3:E" & myrow & ")"

    Sheet1.Columns().AutoFit

End Sub

I am not getting any errors when running the macro but it doesn't paste the calculated sum in the column (Sum).

Looking forward for your answer and Thanks for your help in advance!

Answer 1

Try the following. I have provided several options that you might be interested in.

The following is simply your code edited to make it work.

Sub Macro()

    Dim myrow As Long

    Windows("techdata.xlsm").Activate

    Sheet1.Activate

    Range("A16").Select 'Need to identify the starting cell first

    ActiveCell.FormulaR1C1 = "Sum"

    ActiveCell.Offset(0, 1).Select  'Activte one cell to right of starting cell

    myrow = Range("E6666").End(xlUp).Row

    ActiveCell.Formula = "=SUM(E3:E" & myrow & ")"

    Sheet1.Columns().AutoFit

 End Sub

However, it is almost never necessary to actually select cells. Most code can be written by simply referencing the required cells like the following code example.

Sub Macro_2()

    Dim myrow As Long

    Windows("techdata.xlsm").Activate

    Sheet1.Activate

    Range("A16") = "Sum"    'Need to identify the starting cell

    myrow = Range("E6666").End(xlUp).Row    'Last used cell in column E

    Range("A16").Offset(0, 1).Formula = "=SUM(E3:E" & myrow & ")"

    Sheet1.Columns().AutoFit

 End Sub

Both of the above solutions require "Sum" to be in cell A16. If you insert additional rows under your existing data table at the top then it will no longer be cell A16. Therefore if you select the cell with Sum in it and create a name for it by using Formula ribbon -> Define name and give the cell a name (eg.  SumCell) then if you insert or delete rows above it then the name assigned to the cell moves with the cell and you can then reference that cell by its name like the following code example.

Sub Macro_3()

    Dim myrow As Long

    Windows("techdata.xlsm").Activate

    Sheet1.Activate

    Range("SumCell") = "Sum"    'Need to identify the starting cell

    myrow = Range("E6666").End(xlUp).Row    'Last used cell in column E

    Range("SumCell").Offset(0, 1).Formula = "=SUM(E3:E" & myrow & ")"

    Sheet1.Columns().AutoFit

 End Sub

It is even possible to create the entire "Approvals Summary" table a specified number of rows below the last data in the table above. If you want an option like this then please get back to me.

Answer 2

I am having some difficulty understanding exactly what you are attempting to do. However, I have tried to guess a bit and maybe the following will help. Note the comments and where I have commented out lines and replaced with what I think that you want.

Sub Macro()

    Dim myrow As Long   'Good programming practice to dimension all variables

    Windows("techdata.xlsm").Activate

    Sheet1.Activate

    Range("G1").Select              'Select a starting point in the worksheet (Where Sum goes)

    ActiveCell.FormulaR1C1 = "Sum"  'Inserts Sum in the currently active cell

    'ActiveCell.Offset(0, 1).Select  'Selects cell 1 column to right of previous selection*(Why?)***

    ActiveCell.Offset(1, 0).Select  'This is one row down under Sum

    'myrow = Range("E6666").End(xlUp).Row    '??? Depends on column contents what row this will be

    myrow = Cells(Rows.Count, "E").End(xlUp).Row     'Example for last used row in column E

    'ActiveCell.Offset(0, 4).Formula = "=SUM(E3:E" & myrow & ")"  'If to be under Sum they why the offset?

    ActiveCell.Offset.Formula = "=SUM(E3:E" & myrow & ")"   'Places formula under Sum

    Sheet1.Columns().AutoFit

 End Sub

Answer 3

Hello OssieMac,

Thanks for the reply and i am sorry for not elaborating the details.

What i am doing is  summing the column (TOTAL_DLR_AM) and putting the calculated sum in the row named sum. I am able to calculate the sum in the column (TOTAL_DLR_AM) by the below code:

myrow = Range("E6666").End(xlUp).Row

ActiveCell.Offset(0, 4).Formula = "=SUM(E3:E" & myrow & ")"

Now i want to put the calculated sum in the row named sum, so how do i do that.

Below is the workbook image.

Answer 4

Hello OssieMac,

Thanks A lot man!  It has fixed my problem.

I am gone go with your last example as there could be more or less data in the table.

Regards

Wither