Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    VBA Code for SUMIF

    I have a spreadsheet where I want all the values in Column D that corresponds to text "CURRENT CLOSING BALANCE" to total using SUMIF. If I use the SUMIF formula manually I get the correct vanswer, but gett zero when using the sumif formula in a macro. See attached workbook

    My code is as follows:


    HTML Code:
    Finalrow = Range("D65536").End(xlUp).Row
    Range("C" & Finalrow + 3).Value = "CURRENT CLOSING BALANCE"
    Range("D" & Finalrow + 3).Formula = "=sumif(C1:D" & Finalrow  & ", ""CURRENT CLOSING BALANCE"", D1:D" & Finalrow & ")"
    Your assistance in resolving this will be most appreciated
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Why a macro when a spreadsheet formula would suffice?

    =SUMIF(C:C ,"*CURRENT CLOSING BALANCE*", D : D )

    I put the * around CURRENT CLOSING BALANCE because you have spaces in those words.

    You can remove the spaces from the D's. The website was turning it into a smilie.

    or for the macro, try

    Code:
    Sub sumcurrent()
        Dim Finalrow As Long
        Finalrow = Range("D65536").End(xlUp).Row
        Range("C" & Finalrow + 3).Value = "CURRENT CLOSING BALANCE"
        Range("D" & Finalrow + 3).Formula = "=sumif(C1:D" & Finalrow & ", ""*CURRENT CLOSING BALANCE*"", D1:D" & Finalrow & ")"
    End Sub
    Last edited by jrb; 2012-05-09 at 13:39.

  3. The Following User Says Thank You to jrb For This Useful Post:

    HowardC (2012-05-09)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, code works perfectly

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JRB,

    To prevent problems with formulas converting to smilies place them in [noparse] [*noparse] tags.
    Ex: =SUMIF(C:C ,"*CURRENT CLOSING BALANCE*", D:D )
    Of course I had to replace the / {backslash} for the ending tag with a * so I could nest the tags.
    Last edited by RetiredGeek; 2012-05-09 at 15:32.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    jrb (2012-05-09)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •