sumif in vba

[tpkelley_no]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> I am trying to write the following sumif in VBA. The formula will go in column D last row plus one down. Column b will start in B6 and end last row 17 cells up. Column j will start in j6 and end 2 rows up. I came up with the following vba but I get sytax error. the bold is the error
=sumif(b6:b734,”SUB”,j6:j734)

<!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]-->
<!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable     {mso-style-name:"Table Normal";     mso-tstyle-rowband-size:0;     mso-tstyle-colband-size:0;     mso-style-noshow:yes;     mso-style-parent:"";     mso-padding-alt:0in 5.4pt 0in 5.4pt;     mso-para-margin:0in;     mso-para-margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Times New Roman";     mso-ansi-language:#0400;     mso-fareast-language:#0400;     mso-bidi-language:#0400;} </style> <![endif]-->  Sub test()
  Dim cost_RBsum As Range
  Dim SUBAC As Range
  Set SUBAC = Range("B6000").End(xlUp).Offset(-17, 0)
  Set tctd = Range("j6000").End(xlUp).Offset(-2, 0)
  Set cost_RBsum = Range("d6000").End(xlUp).Offset(1, 0)
  [B]cost_RBsum.Formula = "sumif("B6:B"&"SUBAC",""SUB"","J6:J" & "tctd")"[/B]
  End Sub

<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

[Michael M]

Try

cost_RBsum.Formula = "=sumif(B6:B" & SUBAC & ",""SUB"",J6:J" & tctd & ")"

 

[JackDanIce]

I do not think you're creating the formula string correctly to pass back as a formula to the cell. Have you tried recording a macro to do these actions and reading the resulting code?

This is one way how to code above, which hopefully provides the correct SUMIF formula (untested):

Sub test_1()
  
  Dim rng1          As Range
  Dim rng2          As Range
  Dim rng3          As Range
  
  Dim tempFormula   As String
  
  Set rng1 = Cells(Rows.Count, 2).End(xlUp).Offset(-17)
  Set rng2 = Cells(Rows.Count, 10).End(xlUp).Offset(-2)
  Set rng3 = Cells(Rows.Count, 4).End(xup).Offset(1)
  
  tempFormula = "=SUMIF(B6:B@1, ""SUB"", J6:J@2)"
  tempFormula = Replace(tempFormula, "@1", rng1.Row)
  tempFormula = Replace(tempFormula, "@2", rng2.Row)
  
  rng1.Formula = tempFormula
  
  Set rng1 = Nothing
  Set rng2 = Nothing
  Set rng3 = Nothing
  
End Sub

 

[Marcelo Branco]

Or maybe this...

Sub test()
    Dim cost_RBsum As Range, SUBAC As [COLOR=#ff0000]Long[/COLOR]
    
    SUBAC = Range("B6000").End(xlUp).Offset(-17).[COLOR=#ff0000]Row[/COLOR]
    Set cost_RBsum = Range("d6000").End(xlUp).Offset(1)
    cost_RBsum.Formula = "=sumif(B6:B" & SUBAC & ",""SUB"",J6:J" & SUBAC & ")"
End Sub

M.