Inputing a formula in to a selected cell in a table

[luolovepi]

I have a worksheet called "Pr", and a table in "Pr" called "Table1".
Size of "Table1" can be changed at anytime. So I wrote a sub "subtotal" for dynamically puting a SUM formula into the third last cell in "Table1" column 12 to sum all the cells' values above.
But when I tried to run it, how come no formula was added to the cell?

Public Sub subtotal()
    Dim subtotal As Range
    With Pr.ListObjects("Table1")
        Set subtotal = .ListColumns(12).DataBodyRange.Offset(1, 0). _
                                Resize(.DataBodyRange.Rows.count - 3).End(xlUp)
    End With
    subtotal.Formula = "=SUM(M15:M" & subtotal.row - 1 & ")"
End Sub

 

[Peter_SSs]

Firstly, a few comments about your code.

1. It is not a good idea to name a procedure the same as any vba keyword (subtotal)

2. It is not a good idea to use a variable name the same as the name of your procedure or a vba keyword (subtotal again)

3. I'm not sure why you have .End(xlUp) in your code? Depending on what is in your table, that could easily cause your subtotal range to refer to the top row in your table or sheet.

4. You are using 'Pr' in your code without defining what it is.

I'm guessing that your table data starts in B15 and you want to put the formula in the 3rd last row of the 12th column of your table (ie column M). Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> sub_total()<br>    <SPAN style="color:#00007F">Dim</SPAN> stotal <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Pr <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Pr = Sheets("Pr")<br>    <SPAN style="color:#00007F">With</SPAN> Pr.ListObjects("Table1")<br>        <SPAN style="color:#00007F">Set</SPAN> stotal = .ListColumns(12).DataBodyRange.Offset( _<br>            .DataBodyRange.Rows.Count - 3).Resize(1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    stotal.FormulaR1C1 = "=SUM(R15C:R[-1]C)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

If this is not what you want you'll need to tell us more about your table and what you are trying to achieve.

 

[shadow12345]

**** it too slow.... i was so close to doing that one Peter wins again.

 

[luolovepi]

Thank you so much Peter. It works perfectly!


Best regards,
lolo

Firstly, a few comments about your code.

1. It is not a good idea to name a procedure the same as any vba keyword (subtotal)

2. It is not a good idea to use a variable name the same as the name of your procedure or a vba keyword (subtotal again)

3. I'm not sure why you have .End(xlUp) in your code? Depending on what is in your table, that could easily cause your subtotal range to refer to the top row in your table or sheet.

4. You are using 'Pr' in your code without defining what it is.

I'm guessing that your table data starts in B15 and you want to put the formula in the 3rd last row of the 12th column of your table (ie column M). Try this in a copy of your workbook.


Public Sub sub_total()
Dim stotal As Range
Dim Pr As Worksheet

Set Pr = Sheets("Pr")
With Pr.ListObjects("Table1")
Set stotal = .ListColumns(12).DataBodyRange.Offset( _
.DataBodyRange.Rows.Count - 3).Resize(1)
End With
stotal.FormulaR1C1 = "=SUM(R15C:R[-1]C)"
End Sub



If this is not what you want you'll need to tell us more about your table and what you are trying to achieve.

 

[luolovepi]

lol.. thank you too Shadow!

**** it too slow.... i was so close to doing that one Peter wins again.

 

[Peter_SSs]

Thank you so much Peter. It works perfectly!

Glad to help. Thanks for the feedback.

**** it too slow.... i was so close to doing that one Peter wins again.

Sorry about that.