JeffT
02-29-2012, 09:01 AM
Hi
I have two sheets in the same workbook which have the same layout. I have 27 columns B:AB which contain an integer in some cells related to a name in the 28th Column AC. What I'm trying to do is add the values in one sheet to the corresponding cells in the second sheet. Eg B6 on the first sheet is added into B6 in the second sheet. Similarly for C6, D6 etc The first sheet contains the monthly sums & the second sheet will contain the sum from the start of the record. The 28th column on the two sheets may be contain different names, the code will check till it finds the row with the corresponding name and then sum the data here. If the name doesn't exist it'll add it to the bottom then sum with the zero values in the adjacent columns.
Is there an easy way to do this using an array or range or similar? I can do a simple loop summing each cell individually using Offset (or something similar), I just wondered if there was a more elegant / quicker solution.
e.g.
Let Year!Range(B6:AB6).value = Year!Range(B6:AB6).value + Month!Range(B6:AB6).value.
This doesn't work but shows the type of thing I mean. The cell references would be decided by the code.
The sheet may hold 100 rows (possibly more) and there are 2 sets of 27 columns (one each side of the 28th column) which means a possible 5400 sums so I need the quickest way.
Hope you can help. I'd appreciate being told if a loop is the only way, which I can do.
Thanks for all your help
Jeff T
I have two sheets in the same workbook which have the same layout. I have 27 columns B:AB which contain an integer in some cells related to a name in the 28th Column AC. What I'm trying to do is add the values in one sheet to the corresponding cells in the second sheet. Eg B6 on the first sheet is added into B6 in the second sheet. Similarly for C6, D6 etc The first sheet contains the monthly sums & the second sheet will contain the sum from the start of the record. The 28th column on the two sheets may be contain different names, the code will check till it finds the row with the corresponding name and then sum the data here. If the name doesn't exist it'll add it to the bottom then sum with the zero values in the adjacent columns.
Is there an easy way to do this using an array or range or similar? I can do a simple loop summing each cell individually using Offset (or something similar), I just wondered if there was a more elegant / quicker solution.
e.g.
Let Year!Range(B6:AB6).value = Year!Range(B6:AB6).value + Month!Range(B6:AB6).value.
This doesn't work but shows the type of thing I mean. The cell references would be decided by the code.
The sheet may hold 100 rows (possibly more) and there are 2 sets of 27 columns (one each side of the 28th column) which means a possible 5400 sums so I need the quickest way.
Hope you can help. I'd appreciate being told if a loop is the only way, which I can do.
Thanks for all your help
Jeff T