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.

[TABLE="width: 255"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Paid Breaks[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Shift (Hours)[/TD]
[TD]Break Time Available[/TD]
[TD]Break Time Used[/TD]
[/TR]
[TR]
[TD]Agent1[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:18:17[/TD]
[/TR]
[TR]
[TD]Agent2[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:16:14[/TD]
[/TR]
[TR]
[TD]Agent3[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:11:23[/TD]
[/TR]
[TR]
[TD]Agent4[/TD]
[TD]6.00[/TD]
[TD]00:14:24[/TD]
[TD]0:14:57[/TD]
[/TR]
[TR]
[TD]Agent5[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:16:43[/TD]
[/TR]
[TR]
[TD]Agent6[/TD]
[TD]4.00[/TD]
[TD]00:09:36[/TD]
[TD]0:10:53[/TD]
[/TR]
[TR]
[TD]Agent7[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:18:20[/TD]
[/TR]
[TR]
[TD]Agent8[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:15:11[/TD]
[/TR]
[TR]
[TD]Agent9[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:16:24[/TD]
[/TR]
[TR]
[TD]Agent10[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:08:10[/TD]
[/TR]
[TR]
[TD]Agent11[/TD]
[TD]7.00[/TD]
[TD]00:16:48[/TD]
[TD]0:16:29[/TD]
[/TR]
</tbody>[/TABLE]

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