How do I make my SumIf Function run through until last range of my data on VBA Code-CodePudding

Here is my SumIf Formula

=SUMIF($C$26:$C$43,$C$6,D26:D43)

The C6 is fixed so I lock it so the cell won't run if I drag it

The problem is the range and the sum range

The starting row column of the range (C26) and the sum range (D26) are always the same but not for the last row because my data quantity is not always the same (sometimes it goes until 100 sometimes less)

How can I change the VBA formula of the SumIf to match exactly the same quantity of my data?

here's my SumIf VBA Code (Got it from the record it)

"=SUMIF(R26C3:R43C3,R6C3,R[20]C:R[37]C)"

CodePudding user response：

Please, try the next way. The code must determine the last cell in C:C and then use it. There are more possibilities to do that. As the following code does, or using directly in the string formula, replacing (existing) 43 ("=SUMIF($C$26:$C$" & lastR ...:

Sub formulaUntilLast()
  Dim lastR As Long, rngC As Range, RngD As Range, strFormula As String
  lastR = ActiveSheet.Range("C" & rows.count).End(xlUp).row
  Set rngC = Range("C26:C" & lastR)
  Set RngD = Range("D26:D" & lastR)

  strFormula = "=SUMIF(" & rngC.Address & ",$C$6," & rngC.Address & ")"
  Debug.Print strFormula
End Sub