PDA

View Full Version : Solved: Sum individual cell values in two Ranges



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

mdmackillop
02-29-2012, 11:13 AM
Can you post a simple example?

JeffT
02-29-2012, 01:34 PM
Hi mdmackilop

Sorry for the delay, I went home then had to write the code below and try it out. This only works on a single row at present but it's the sort of code I'd use if there isn't another way. The m = 6 is for the next stage when searching for the names. I just want to sum the data row by row if possible in the way this code does one at a time.

I've uploaded the file as well. The code below runs by using the red button on the "Input" sheet and adds the first row of figure from AD6:BD6 to the "Total Data" sheet. eventually it'll addd the data from the equivalent "Name" from both sides of the Names column AC. the charts will be linked as well. Only the first two are linked properly at the moment using a hidden sheet "Data" .

Sorry its not that simple, but its difficult to make it simple.


Sub Add_Subs() ' This checks if the subcontractor is already on the "Total" chart
' if so it adds the scores, if not it adds the subcontractor & scores
Dim MonthValue As Integer

Let m = 6 ' Start condition
For p = 1 To 27

Sheets("Input").Select

Let DataSub = Range("AC" & m).Activate '.Value

If DataSub = Null Then End

ActiveCell.Offset(rowOffset:=0, columnOffset:=p).Activate

Let MonthValue = ActiveCell.Value

Sheets("Total_Data").Select

Range("AC" & m).Activate

ActiveCell.Offset(rowOffset:=0, columnOffset:=p).Activate

Let ActiveCell.Value = ActiveCell.Value + MonthValue

Next p

Sheets("Input").Select

Range("AC6").Activate

End Sub

mdmackillop
02-29-2012, 04:50 PM
Give this a try
Sub Add_Subs() ' This checks if the subcontractor is already on the "Total" chart
' if so it adds the scores, if not it adds the subcontractor & scores

Dim rSource As Range
Dim rTarget As Range
Dim cel As Range, tgt As Range

Application.ScreenUpdating = False
With Sheets("Input")
Set rSource = Range(.Cells(6, 29), .Cells(Rows.Count, 29).End(xlUp))
End With

With Sheets("Total_Data")
Set rTarget = Range(.Cells(6, 29), .Cells(Rows.Count, 29).End(xlUp))

For Each cel In rSource
Set tgt = rTarget.Find(cel, lookat:=xlWhole)
If tgt Is Nothing Then
Set tgt = .Cells(Rows.Count, 29).End(xlUp)(2)
tgt.Value = cel.Value
End If
'Copy Right
cel.Offset(, 1).Resize(, 27).Copy
tgt.Offset(, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
'Copy Left
cel.Offset(, -27).Resize(, 27).Copy
tgt.Offset(, -27).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next
.Activate
tgt.Select
Application.CutCopyMode = False
End With
Application.ScreenUpdating = True
End Sub

JeffT
03-01-2012, 12:49 AM
Thanks for that, I'll give it a try tonight.

Looking at the code I think it may sum all the cells in each of the same rows. I need to sum the rows individually as which of the rows from the different sheets are summed is dependent on the name in the centre column. These will change position / be added to etc. However even if I'm correct I'm sure I can make this do what I need so thanks again.

I'll let you know

Regards

Jeff

mdmackillop
03-01-2012, 06:34 AM
The Find function looks for the row where the data is added.

Set tgt = rTarget.Find(cel, lookat:=xlWhole)

JeffT
03-01-2012, 01:55 PM
Hi mdmackilop

As ever some very elegant code that just works.

A lot of new code I've not used before e.g.

Operation:=xlAdd

End(xlUp))

So lots to learn & understand, hopefully I'll be able to incorporate it in other code in the future.

Thank you

Regards Jeff

mdmackillop
03-01-2012, 04:18 PM
Try the macro recorder. You get a lot of verbiage but a few gems!

JeffT
03-02-2012, 01:04 PM
Yes I do that sometimes as well as robbing code from previous things. I find "Help" very useful quite a lot of code in there. The big problem as always with Help is knowing what to search for.

Thanks again

Regards

Jeff