PDA

View Full Version : Calculate multiple sums depending on cells

Aschrum
09-23-2013, 02:42 AM
Hi,

I want to sum up (K-) cells depending on the first symbol of another (C-) cell in the same line(s) and post it into an Outlook body.

The cells could be
C16=1a..., K16=5
C17=1b..., K17=5
mysum1=10

Dim mysum1 As Integer
Dim mysum2 As Integer
...
For Each c In ws.Range("C16", ws.Cells(ws.Rows.Count, 3).End(xlup))
If Left(c.Value, 1) = "1" Then
mysum1 = xl.WorksheetFunction.sum(ws.Range("K16:K18"))
ElseIf Left(c.Value, 1) = "2" Then
mysum2 = xl.WorksheetFunction.sum(ws.Range("K19:K24"))
...
ElseIf Left(c.Value, 1) = "45" Then
mysum45 = xl.WorksheetFunction.sum(ws.Range("K28:K34"))
End If
Next
...
.Body = "Bandbeschichtung: " & mysum1 _
& vbCrLf & "Walz+Fin " & mysum2 _

Obviously the part of the Range within the loop doesn't depend on the first symbol of the c-row so far, but anyways:
It only posts mysum1, no other sum works. :think:

Thanks in advance for any help.

André

snb
09-23-2013, 03:58 AM
For Each c In ws.Range("C16", ws.Cells(ws.Rows.Count, 3).End(xlup))
.body ="Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(choose(val(Left(c.Value, 2)) mod 45 +1,"K28:K34", "K16:K18","K19:K24")))
Next

Aschrum
09-23-2013, 05:55 AM

Just recognized the line "45" is likely to be missunderstood.
It should be like "4" and/or "5". So any K-cell related to C-cells with first character 4 AND 5 should be summed up to mysum45 instead of first two characters 45, so (c.Value, 1) was right in this regard.
I guess the MOD-Operator doesn't help my issue. There are six groups from 1-9. (Only six, cause there are some like mysum45)
After googling I suppose it should be the OR-operator, like ElseIf Left(c.Value, 1) = "4" Or "5" Then... <- just checked it. Works fine. :)

Would be nice your way to be able to write each "sum" (without diming and calculating it beforehand) in the body.
But it seems If is not allowed within the body.
The following shows a failure message: .body = "Bandbeschichtung: " & If Left(c.Value, 1) = "2" Then xl.WorksheetFunction.sum(ws.Range("K16:K18"))

Why doesn't the For Each loop through all C's instead of calculating only mysum1?

snb
09-23-2013, 07:42 AM
If you write after the loop only the last value will be written.

No need for any ElseIf
You can write directly in .Body (unless you make a syntax error as you did).

For Each cl In ws.columns(3).specialcells(2).offset(15).specialcells(2)
.body ="Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(choose(val(Left(cl.Value, 2)),"K16:K18","K19:K24","K28:K34","K28:K34")))
Next

Aschrum
09-24-2013, 08:13 AM
Somehow this kills the complete mailitem. :(

.Body = "Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(Choose(Val(Left(c.Value, 1)), "K16:K18", "K19:K24", "K28:K34", "K28:K34")))

snb
09-24-2013, 12:37 PM
It doesn't; test the code using

msgbox "Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(Choose(Val(Left(c.Value, 1)), "K16:K18", "K19:K24", "K28:K34", "K28:K34")))

Aschrum
09-25-2013, 01:01 AM
Doesn't work here either. Yes, I've tried it within For each... Next
Is some of the code not working in Office 2007?

This works, but doesn't help the issue.
MsgBox (xl.WorksheetFunction.sum(ws.Range("K16:K18")))

By the way: I think the "choose"-way is a bad track.
It has no indication regarding the first symbol in C to its K-cells. As a consequence it shouldn't be possible to do the final step as an automated "selection"/addition of what I've tried to explain here

The cells could be
C16=1a..., K16=5
C17=1b..., K17=5
mysum1=10

snb
09-25-2013, 03:04 AM
Since you are providing too little information it's not possible to give any sound advice.

Aschrum
09-26-2013, 01:19 AM
Yeah, sure.

10620
I didn't finish it the list. It should end with a C-cell containing a 8 or 9 as first symbol.

As already said there are six groups. 1, 2, 3, 4&5, 6&7, 8&9.
It is possible that a group doesn't appear in some weeks.
Moreover the cells aren't fixed. There could also be ten C-cells beginning with "1" or as said none.
Therefor the sums shouldn't contain fixed cells like "K16:K18" but instead.

In this example SUM1=(K16:18)=8+2+2 whereas "1" in "SUM" is for the first symbol in C.

Did you get me now?