none
how to use convert formula to VBA macro RRS feed

  • Question

  • =SUMIFS(Sheet1!$D:$D,Sheet1!$C:$C,Sheet4!A2)

    I use this formual to extract the info from (sheet1) the raw from  for desired results, could you help me with writing a macro for this formual.

    Everyday we pull raw data from a application to excel sheet and format it into set of required cells using pivot & use this formula to get the necessary information.

    Now I want use macro for that application so that it can give me the below information.

    type        #total #XX addressed #sys addressed #Not Addressed #XX resolved
    ADA         6919    835               6074               10                         835
    CBM         2185          2176              0                  9                           2175
    HDLT 115            83                      0                  32                         83
    IM         291           283              0                     9                         283
    KLE         275           263              0                   12                        262
    PPAD 2439  2435              0                    4                         2435
    PL         921             31             27                   863                       31
    RSTRC 5568 5407              0                   161                      5404
    RMCDUP 40402  36582             2562             1258                      36045

    Instead of dumping data from an app to excel and applying formula to it, I want to use a macro for it please help.

    Tuesday, May 2, 2017 8:40 AM

All replies

  • =SUMIFS(Sheet1!$D:$D,Sheet1!$C:$C,Sheet4!A2)

    I use this formual to extract the info from the raw for desired results, could you help me with writing a macro for this formual.

    Actually I want to put a refresher tab in the excel sheet, so that if I click on it the data should be refreshed automatically instead of repeating the usage of formula.


    • Edited by KVG9 Tuesday, May 2, 2017 8:44 AM
    Tuesday, May 2, 2017 8:43 AM
  • Replacing a sheet formula with a macro is easy- in this case, you could just call

    application.worksheetfunction(SUMIFS...[etc])

    and pass in variables holding the source ranges. However, moving this to VBA doesn't really get you additional efficiency. In your specific case, what is undesirable about using the formula? Are you worried that users may mess with the formula (in which case, locked cells may be needed)? Do you want to retain an old value, even if the value in Sheet4!A2 is changed? Or do you need to cycle through a set of values that might show up in Sheet4!A2, and place each SUMIF value in a different cell (in which case, separate sumifs in each cell is still an alternative)?

    Just trying to better understand what you are trying to do :)

    Tuesday, May 2, 2017 3:58 PM
  • Replacing a sheet formula with a macro is easy- in this case, you could just call

    application.worksheetfunction(SUMIFS...[etc])

    and pass in variables holding the source ranges. However, moving this to VBA doesn't really get you additional efficiency. In your specific case, what is undesirable about using the formula? Are you worried that users may mess with the formula (in which case, locked cells may be needed)? Do you want to retain an old value, even if the value in Sheet4!A2 is changed? Or do you need to cycle through a set of values that might show up in Sheet4!A2, and place each SUMIF value in a different cell (in which case, separate sumifs in each cell is still an alternative)?

    Just trying to better understand what you are trying to do :)

    Tuesday, May 2, 2017 3:59 PM
  • Hi,

    I notice you are looking for a macro for your formula. To get more suggestion, I would move the thread to Excel for Developers forum for more help.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, May 3, 2017 1:59 AM
  • Hello,

    I suggest you use pivottable to filter and count the data. For more information, please visit 

    Create a PivotTable to analyze worksheet data

    To do it programmatically, i suggest you record a macro. Please visit 

    Automate tasks with the Macro Recorder

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 4, 2017 5:29 AM
    Moderator