VBA SUM Variable Range

Question

enter image description here

I want a code to sum the Variable rows up if certain condition is met. e.g. If A12 is numeric and B12 is empty then insert a fomula in cell C12 to sum C3:C11. Then perform the same action at C22 and C30. The problem I have is don't know how to define the starting row.

Sub Test()
Dim y As Variant
Dim r As Variant
Dim StartRow As Variant

   LastRow = Range("C" & Rows.Count).End(xlUp).Row
        For y = 3 To 500
            For r = 1 To LastRow

            If InStr(1, Cells(r, 1), "Amount") Then
                StartRow = r

            If IsNumeric(Cells(y, 1)) And IsEmpty(Cells(y, 2)) Then
            Cells(y, 3).Formula = "=SUM(C" & StartRow + 1 & ":C" & y - 1 & ")"
            End If
         End If
      Next r
  Next y

End Sub

You could do this with just an extra column and without needing VBA... — LS_ᴅᴇᴠ
– LS_ᴅᴇᴠ, Commented Jun 18, 2015 at 14:13
@LS_dev Since this is just a part of my whole code, so I want the VBA code to perform this, could you please help? ^ ^ — Nicholas Kan
– Nicholas Kan, Commented Jun 18, 2015 at 14:15

Abe Gold · Accepted Answer · 2015-06-18 17:07:35Z

4

Sub Test()
Dim y As Variant
Dim firstRow As Variant
Dim lastRow As Variant
lastRow = Range("C" & Rows.Count).End(xlUp).Row
firstRow = Cells(lastRow, 3).End(xlUp).Row
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
    Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")"
End If
For y = firstRow To 3 Step -1
    lastRow = Cells(y, 3).End(xlUp).Row
    firstRow = Cells(lastRow, 3).End(xlUp).Row
    If firstRow < 3 Then firstRow = 3
    If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
        Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")"
    End If
    y = firstRow
    If firstRow = 3 Then Exit Sub
  Next y
End Sub

edited Jun 18, 2015 at 17:07

answered Jun 18, 2015 at 16:13

Abe Gold

2,38718 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nicholas Kan Over a year ago

Hi Abe, Excellent! It works! But May I know how to insert this condition? If IsNumeric(Cells(y, 1)) And IsEmpty(Cells(y, 2)) Then

Abe Gold Over a year ago

Edited the answer to include that. Let me know if it doesn't work. I did not test it.

Nicholas Kan Over a year ago

Hi Abe, this works perfectly! but...Still a question, the formula in C12 becomes SUM(C1:C11), what I want is C3:C11. Which part should I amend?

Nicholas Kan Over a year ago

HaHa, I also changed For y = firstRow To 3 Step -1 from 1 to 3 before, but it does not work, that's why I have to ask you.

Abe Gold Over a year ago

There are a few other changes as well. Replace you code with this and see if it works.

|

Collectives™ on Stack Overflow

VBA SUM Variable Range

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related