Msgbox to return sum of 2 cells

[mulholm]

Hi,

I am looking for some help to expand the formula i have. On the table below i want a message box to appear when column D is higher then column C which i have managed to code myself. However where i am struggling with is that i want the message box to display how much higher D is than C.

Paid Breaks
Name	Shift (Hours)	Break Time Available	Break Time Used
Agent1	7.00	00:16:48	0:18:17
Agent2	7.00	00:16:48	0:16:14
Agent3	7.00	00:16:48	0:11:23
Agent4	6.00	00:14:24	0:14:57
Agent5	7.00	00:16:48	0:16:43
Agent6	4.00	00:09:36	0:10:53
Agent7	7.00	00:16:48	0:18:20
Agent8	7.00	00:16:48	0:15:11
Agent9	7.00	00:16:48	0:16:24
Agent10	7.00	00:16:48	0:08:10
Agent11	7.00	00:16:48	0:16:29

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Just now i have the below code:

Private Sub DataAnalysis()


Dim Overbreaks As Integer


For i = 3 To 13


If Sheet1.Range("D" & i).Value > Sheet1.Range("C" & i).Value Then

Sheet1.Range("D" & i).Select

Overbreaks = ActiveCell.Value + Sheet1.Range("C" & i)

MsgBox (Overbreaks)

End If


Next


End Sub

However this doesnt seem to work.

I also want in the message box to return "Agent name (Column A) was over their break by Overbreaks (Column D - Column C)"

 

[steve the fish]

Try this:

For I = 3 To 13
    If Sheet1.Range("D" & I).Value > Sheet1.Range("C" & I).Value Then
        MsgBox Sheet1.Range("A" & I).Value & " was over their break by " & _
            Format(Sheet1.Range("D" & I).Value - Sheet1.Range("C" & I).Value, "hh:mm:ss")
    End If
Next

 

[mulholm]

worked perfectly.

is there a way for it to all appear on the 1 msgbox instead of clicking ok then the next one appears.
Also can you e-mail the output of the msgbox once it's generated?

 

[steve the fish]

Something like:

For i = 3 To 13
    If Sheet1.Range("D" & i).Value > Sheet1.Range("C" & i).Value Then
        If Len(myMessage) = 0 Then
            myMessage = Sheet1.Range("A" & i).Value & " was over their break by " & _
                Format(Sheet1.Range("D" & i).Value - Sheet1.Range("C" & i).Value, "hh:mm:ss")
        Else
            myMessage = myMessage & vbLf & Sheet1.Range("A" & i).Value & " was over their break by " & _
                Format(Sheet1.Range("D" & i).Value - Sheet1.Range("C" & i).Value, "hh:mm:ss")
        End If
    End If
Next
If Len(myMessage) > 0 Then MsgBox myMessage

Look online for email example code and have myMessage as the body.

 

[mulholm]

Got it
i already had the e-mail part it was just the bit in between.
Thank you so much