help with xlTotalsCalculationSum

    ActiveSheet.ListObjects("Table_pastel_12_cust_rank").ListColumns("2019-032"). _
    TotalsCalculation = xlTotalsCalculationSum



    Hi there

    below is my macrofrom a sheet where i import data from mysql database. the field names are not always the same and keeps changing. thus above example will not help me.


    I need something like to make the calculation - show totals auto sum for column c.


    ActiveSheet.ListObjects("$C").ListColumns("$C"). _
    TotalsCalculation = xlTotalsCalculationSum


    Edited once, last by royUK ().

    The code could still do with a tidy up. There's lots of unnecessary line lines like the scrolling an selecting, all of which will slow down the code. This is because the code has been recorded, which is fine but the recorder will catch every move that you make, much of it unnecessary with VBA.


    Try this, I have cleaned it up.


    Quote from royUK

    pvzshark



    You should have read the Rules by now. Next time you cross post or break any other rules I will close your posts.

    Super moderator - please answer my query - its the important thing here - if you do not know how to please foreward someone who can.

    Quote from royUK

    The code could still do with a tidy up. There's lots of unnecessary line lines like the scrolling an selecting, all of which will slow down the code. This is because the code has been recorded, which is fine but the recorder will catch every move that you make, much of it unnecessary with VBA.


    Try this, I have cleaned it up.


    Thanks for the cleaning up - Yes i am ph mysql programmer trying to pull data into excell, Only learning vba -so saw that recording macro helps a lot, but only this missing is to find the last entry and do a calculation, but this must change when a filter is added, i can finsd last entry, but it doen not change values when i add filter

    The macro recorder is a good way to learn VBA, but you must also learn how to clean the code up.


    What calculation do you want to do? Can you attach a small dummy file with some data, remove any private data

    Quote from royUK

    The macro recorder is a good way to learn VBA, but you must also learn how to clean the code up.


    What calculation do you want to do? Can you attach a small dummy file with some data, remove any private data

    Hi - i have this


    ActiveSheet.ListObjects("Table_pastel_12_cust_rank").ListColumns(6). _

    TotalsCalculation = xlTotalsCalculationSum


    but in stead of sum i need the last row of that column - totals column to do below query


    IF(AND(RC[-2]=0,RC[-3]=0),0,IF(AND(RC[-2]>0,RC[-3]=0),-100,IF(AND(RC[-2]=0,RC[-3]>0),100,IF(AND(RC[-2]>0,RC[-3]>0),(RC[-2]-RC[-3])/RC[-2]))))



    I am trying to calculate growth on the subtotals.

    Can I ask why you need to set the sheet up with VBA code? Maybe a template sheet would be better.


    Does that formula in the Total Row work when entered manually?

    Quote from royUK

    Can I ask why you need to set the sheet up with VBA code? Maybe a template sheet would be better.


    Does that formula in the Total Row work when entered manually?

    Due to constraints in pivot tables in excel, not being able to add fields extra inbetween 2 date, i have created a stored proc in my sql that creates the pivot table, then i insert data into excell as a table, then after every 2 columns i add 2 more, ist is to get the difference between the 2 columns, second one is to get the growth. the formula works 100% it gets the last column and then sums it. I have tried various other caclulations for the calculated last field but when i filter it still calculates all the rows and not just the filtered. so this method seems to be the best - but it sums, and i need a calculation. hope it makes sense

    Quote from pvzshark

    Due to constraints in pivot tables in excel, not being able to add fields extra inbetween 2 date, i have created a stored proc in my sql that creates the pivot table, then i insert data into excell as a table, then after every 2 columns i add 2 more, ist is to get the difference between the 2 columns, second one is to get the growth. the formula works 100% it gets the last column and then sums it. I have tried various other caclulations for the calculated last field but when i filter it still calculates all the rows and not just the filtered. so this method seems to be the best - but it sums, and i need a calculation. hope it makes sense

    The table columns will always change as well but will always ne 24 - as i bring back financial year and previous finacial year - i do have another conondrum - Cells(3, 5).Value = "Diff" works to change the table heading but Cells(3, 5).Value = substring(c3,6,2)) & "Diff" does not where c3 is another table heading. fe 2018 - 01, 2019 - 01, i need to make this "01-diff"

    Quote from pvzshark

    Due to constraints in pivot tables in excel, not being able to add fields extra inbetween 2 date, i have created a stored proc in my sql that creates the pivot table, then i insert data into excell as a table, then after every 2 columns i add 2 more, ist is to get the difference between the 2 columns, second one is to get the growth. the formula works 100% it gets the last column and then sums it. I have tried various other caclulations for the calculated last field but when i filter it still calculates all the rows and not just the filtered. so this method seems to be the best - but it sums, and i need a calculation. hope it makes sense

    how about


    =Table_pastel_12_cust_rank[[#Totals],[TOT DIFF]]/Table_pastel_12_cust_rank[[#Totals],[CURR TOT]]


    but i need to change the


    =Table_pastel_12_cust_rank[[#Totals],rc[5]]/Table_pastel_12_cust_rank[[#Totals],rc[4]]


    will this work?

    Hi all - I figured it out.


    Code
    1. lastrow = Cells(Rows.Count, 54).End(xlUp).Row
    2. Cells(lastrow, 54).Formula = "=IF(AND(az" & lastrow & "=0,ay" & lastrow & "=0),0,IF(AND(az" & lastrow & ">0,ay" & lastrow & "=0),-1,IF(AND(az" & lastrow & "=0,ay" & lastrow & ">0),1,IF(AND(az" & lastrow & ">0,ay" & lastrow & ">0),(az" & lastrow & "-ay" & lastrow & ")/ay" & lastrow & "))))"

    thanks for everyones input. Regards

    Edited once, last by royUK: Add Code Tags ().

    You must have found it. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules