CODEWITHSUNDEEP
excelvba
Mike
Mike

Reputation: 2751

Find all instances of value in worksheet and sum the offset values

I have wbk1.worksheet(1) and wbk2.worksheet(1).

wbk1.worksheet(1) has a list of values in column A

wbk2.worksheet(2) has the same list of values that may occur multiple times in column A with a number value in the offset(0,1) cell.

I need to do an index or match to find all of values in wbk2 and sum all of the offset(0,1) values. Then take that sum and put it in the offset(0,1) cell in wbk1.worksheets(1).

Example:

Workbook 1, sheet 1
Column A     Column B
value 1
value 2 
value 3

Workbook 2, sheet 1
Column A     Column B
value 1         15
value 2         2 
value 1         3 
value 1         12

End Result:
Workbook 1, sheet 1
Column A     Column B
value 1         30
value 2         2
value 3         0

I've tried doing a for each loop, but I'm still a noob to vb, so clearly not doing something right:

For Each x In rngWbk1
        Set cellrngwbk2 = wbk2.Worksheets(1).Cells.Find(What:=x, LookIn:=xlValues)
        If Not cellrngwbk2 Is Nothing Then
                For Each y In rngwbk1
                    If y = cellrngwbk2 Then
                        total = total + cellrngwbk2.Offset(0, 1).Value
            Else
                    End If
                Next y
                x.Offset(0, 1).Value = total
                total = 0 'resets total value for next x value

        Else
        End If
next x

Upvotes: 0

Views: 1644

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

There is more efficient way. You can use SUMIF formula to calculate values and then rewrite formula with result values. If rngWbk1 corresponds to the values in column A in wbk1.worksheets(1), you can use following code:

 Dim frm As String
 Dim startCell As String

 startCell = Replace(rngWbk1.Cells(1, 1).Offset(0, -1).Address, "$", "")
 frm = "=SUMIF('[" & wbk2.Name & "]" & wbk2.Worksheets(1).Name & "'!A:A," & startCell & ", '[" & wbk2.Name & "]" & wbk2.Worksheets(1).Name & "'!B:B)"

 With rngWbk1.Offset(0, 1)
     .Formula = frm
     .Calculate
     .Value = .Value
 End With

If rngWbk1 doesn't correspond values in column A, you need to adjust startCell in example to startCell = "A2" and change With rngWbk1.Offset(0, 1) to sth like With wbk1.Worksheets(1).Range("B1:B100")

Upvotes: 1

Sam
Sam

Reputation: 497

If VBA is not a requirement, a simple =SUMIF() statement has the same effect.

The function would look something like this: 

=SUMIF([Wbk2.xlsx]Sheet1!A2:A5,Table1[[#This Row],[ID]],[Wbk2.xlsx]Sheet1!B2:B5)

Upvotes: 2

Related Questions