Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop a Procedure from another procedure | Excel Discussion (Misc queries) | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
simplify procedure to get rid of 0 error values in a spreadsheet | Excel Worksheet Functions | |||
Dynamic Values in Procedure | Excel Programming |