Calculate multiple sums depending on cells

[Aschrum]

Hi,

this thread is the successor of this one (http://www.vbaexpress.com/forum/showthread.php?47522-Paste-Excel-content-into-body-of-Outlook-mail).

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]

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]

Thanks for your reply.

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]

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]

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]

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]

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]

Since you are providing too little information it's not possible to give any sound advice.
Post a sample workbook please.

[Aschrum]

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?