ForEach procedure adding values

  • Thread starter Thread starter Mats Westin
  • Start date Start date
M

Mats Westin

Hi,
I've done a ForEach procedure that updates an output list
(based on names in column A, sheet Output) based on a
database (names in column A and values in column B, Sheet
Input).

If there are two identical names in the "database", I want
to add those values in the Output sheet for that name.
(Now I only get the last number in the database)

I appreciate any suggestion how to solve this?

Thanks!
Mats Westin
 
Dim rng as Range, rng1 as Range, cell as Range
Dim res as variant
With Worksheets("Output")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
With Worksheets("Input")
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
for each cell in rng
res = Application.Match(cell,rng1,0)
if not iserror(res) then
cell.offset(0,1).Value = _
rng1(res).Offset(0,1).Value
End if
Next
 
Thanks Tom!

Unfortunately this does not solve the problem! If there
are several items in column A in the input sheet, I want
the values summarised for those items in the output sheet
(under the same item name). Your suggestion results in
taking the first item that match from the input sheet
instead of adding all items with the same name.

Rgds,
Mats
 
Well, your description was ambiguous about that issue - at least to me.

Easily rectified.

Dim rng as Range, rng1 as Range, cell as Range
Dim res as variant
With Worksheets("Output")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
With Worksheets("Input")
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
for each cell in rng
cell.offset(0,1).Value = _
application.Sumif(rng1,cell.value,rng1.offset(0,1))
Next
 
Thanks Tom!
I know that you helped me before and I'm sorry for not
beeing clear enougth to begin with!
Now it works exactly like I want it to!
Thanks a lot!
Rgds,
Mats W
 
Back
Top