ForEach procedure adding values
 

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

ForEach procedure adding values
 

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

--
Regards,
Tom Ogilvy

"Mats Westin" wrote in message
...
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

ForEach procedure adding values
 

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

-----Original Message-----
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

--
Regards,
Tom Ogilvy

"Mats Westin" wrote
in message
...
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


.

ForEach procedure adding values
 

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

--
Regards,
Tom Ogilvy


"Mats" wrote in message
...
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

-----Original Message-----
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

--
Regards,
Tom Ogilvy

"Mats Westin" wrote
in message
...
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


.

ForEach procedure adding values
 

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
-----Original Message-----
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

--
Regards,
Tom Ogilvy


"Mats" wrote in
message
...
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

-----Original Message-----
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

--
Regards,
Tom Ogilvy

"Mats Westin"
wrote
in message
...
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


.



.