VBA Code for SUMIF

[HowardC]

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:

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

[jrb]

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

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

[HowardC]

Thanks for the help, code works perfectly

[RetiredGeek]

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.