CODEWITHSUNDEEP
vbaexcel
MT32
MT32

Reputation: 677

Use VBA to look up a value in a column, then sum up the value in the adjacent column

I have a table like this:

 Name   Variation   Type    No_of_unit_sold

 Apple   FG72H       1         12
 Apple   FG72H       1         17
 Apple   FG72H       1         61
 Apple   FG73H       1          1
 Apple   FG74H       1          3
 Orange  OO20        1          3
 Orange  OO20        1          4
 Orange  OO20        1        117
 Orange  OO20        1          0
 Orange  OO20        1          4
 Orange  OO21        2         15
 Grape   G7TY        1         110
 Grape   G7TY        1          65
 Grape   G7TY        3          53
 Grape   G7TY        3          31
 Grape   G7TY        1          12
 Grape   G7TY        1          1

The actual table is about 39000 rows long.

So I need to look for type 1 (there's 1 to 32) in column Type

then sum up the corresponding No_of_unit_sold and place the result at the bottom row of No_of_unit_sold.

Worksheets("All Fruits").Activate
    Dim i As Integer
    Dim rng As Range

    Worksheets(name).Activate


    lastrow = Range("C2").End(xlDown).Row
    For i = 1 to 32
         Set rng = Range("D:D").Find(what = i)

         '???????????

         Cells(lastrow, 5).Offset(1, 0).Select
         Cells(lastrow, 5).Offset(1, 0) = "=Sum(D2:D" & lastrow & ")"
     Next i
 End Sub

I need suggestion at the ???? part.

Upvotes: 0

Views: 74

Answers (3)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

As @Pᴇʜ said, best solution would be a pure SUMIF. No need of VBA.

In case you need to do it with VBA for any reasons, there is always an VBA version of each formula available in Excel if you use Application.WorksheetFunction Property.

For my code, I used WorksheetFunction.SumIf method

My code just pops up a Msgbox with the sum of No_of_unit_sold if Type=1.

Sub Macro1()
Dim LastRow As Long 'to save the last row number of column TYPE with data

LastRow = Range("C" & Range("C:C").Rows(Range("C:C").Rows.Count).Row).End(xlUp).Row

MsgBox Application.WorksheetFunction.SumIf(Range("C2:C" & LastRow), 1, Range("D2:D" & LastRow))
End Sub

Adapt it tou your needs.

Upvotes: 1

Nandan A
Nandan A

Reputation: 2922

Try this below code. Use if condition to check your criteria and then get the corresponding value from column D and add it to final Total.

Sub Typee()

Dim TotalType As Integer
Dim Total As Integer
Total = 0

TotalType = Range("C2").End(xlDown).Row

For i = 2 To TotalType
If Sheets("Sheet1").Range("C" & i).Value = 1 Then
Total = Total + Sheets("Sheet1").Range("D" & i).Value
End If
Next

MsgBox Total

End Sub

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can do that with formulas only (no need for VBA).

Assuming the data in your example where Type is column C and No_of_unit_sold is column D. You could use in column E:

=SUMIF(C:C,1,D:D)  'to sum all units of type 1
=SUMIF(C:C,2,D:D)  'to sum all units of type 2
…
=SUMIF(C:C,32,D:D) 'to sum all units of type 32

Or what I used in G in the example below is:

=SUMIF(C:C,F:F,D:D)

Note that you need to put the results into a new column, not in the same column as No_of_unit_sold because this would calculate wrong then.

enter image description here

Upvotes: 2

Related Questions