CODEWITHSUNDEEP
excelvba
Sherbetdab
Sherbetdab

Reputation: 127

Sum different range of values in single column

I have a worksheet which produces costings for products, in column I the product totals are grouped, image attached.

enter image description here

Instead of having individual totals i would like a total for each grouped amount, I would like the outcome of column I to look like column K in the image. The amounts aren't always in the same cells and can vary from 1 product to several. I would like to do this with VBA as i already have code written for other parts of the process. Any help would be appreciated.

Below is my code that creates the values in column I

 On Error GoTo ErrorHandler

  FirstRow = Columns(2).Find(What:="NO", Lookat:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Row
  LastRow = Columns(2).Find(What:="NO", Lookat:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row

  Dim r As Range

  With ActiveSheet

  For Each r In .Range("G" & FirstRow & ":" & "G" & LastRow)
        If r.Value <> "" Then
            r.Offset(0, 2).Value = (r.Value * 15.5) / 1000
        End If
        Next r
  End With


ErrorHandler:
  FirstRow = 0
  LastRow = 0

Upvotes: 0

Views: 73

Answers (1)

Naresh
Naresh

Reputation: 3034

I believe that Scott Craner has answered a similar question somewhere with a better excel formula.

Meanwhile, If data is not too huge to make calculations slow then try following in cell K2 and copy it down.

=IF(AND(ISBLANK(I3)=TRUE,ISBLANK(I2)=FALSE),SUM(INDIRECT("I"&LOOKUP(2,1/ISBLANK($I$1:I2),ROW($I$1:I2))&":I"&ROW())),"")

enter image description here

So, if you want to replace the column I based on column G then formula in I2 will be

=IF(AND(ISBLANK(G3)=TRUE,ISBLANK(G2)=FALSE),SUM(INDIRECT("G"&LOOKUP(2,1/ISBLANK($G$1:G2),ROW($G$1:G2))&":G"&ROW()))*15.5/1000,"")

in VBA

With ActiveSheet

For Each r In .Range("G" & FirstRow & ":" & "G" & LastRow)
    If r.Value <> "" Then
    With r.Offset(0, 2)
        .FormulaR1C1 = _
        "=IF(AND(ISBLANK(R[1]C[-2])=TRUE,ISBLANK(RC[-2])=FALSE),SUM(INDIRECT(""G""&LOOKUP(2,1/ISBLANK(R1C7:RC[-2]),ROW(R1C7:RC[-2]))&"":G""&ROW()))*15.5/1000,"""")"
        .Value = .Value
    End With
    End If
Next r
End With

Upvotes: 1

Related Questions