I have a spreadsheet that looks like:
[TABLE="width: 393"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Wrap[/TD]
[TD]Week 40[/TD]
[TD]Week 41[/TD]
[/TR]
[TR]
[TD]Agent 1[/TD]
[TD]0:05:00[/TD]
[TD]0:08:20[/TD]
[/TR]
[TR]
[TD]Agent 2[/TD]
[TD]0:06:40[/TD]
[TD]0:03:20[/TD]
[/TR]
[TR]
[TD]Agent 3[/TD]
[TD]0:05:00[/TD]
[TD]0:05:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0:00:00[/TD]
[TD]0:00:00
[/TD]
[/TR]
</tbody>[/TABLE]
I have the below code to e-mail me to tell me if Week 41 is higher/lower/the same as Week 40:
What i want to the code to do is miss out when column A is blank and then continue until it has reached the end.
Just now it is returning this:
Agent 1 wrap increased by 00:03:20
Agent 2 wrap decreased by 00:03:20
Agent 3 wrap didn't change
wrap didn't change - i don't want this to be here as column A5 has no text.
[TABLE="width: 393"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Wrap[/TD]
[TD]Week 40[/TD]
[TD]Week 41[/TD]
[/TR]
[TR]
[TD]Agent 1[/TD]
[TD]0:05:00[/TD]
[TD]0:08:20[/TD]
[/TR]
[TR]
[TD]Agent 2[/TD]
[TD]0:06:40[/TD]
[TD]0:03:20[/TD]
[/TR]
[TR]
[TD]Agent 3[/TD]
[TD]0:05:00[/TD]
[TD]0:05:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0:00:00[/TD]
[TD]0:00:00
[/TD]
[/TR]
</tbody>[/TABLE]
I have the below code to e-mail me to tell me if Week 41 is higher/lower/the same as Week 40:
Code:
Private Sub WeeklyDataAnalysis()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Do While i <> "#N/A" Or i <> ""
For i = 2 To 5
If Sheet1.Range("C" & i).Value > Sheet1.Range("B" & i).Value Then
If Len(Wrapmessage) = 0 Then
Wrapmessage = Sheet1.Range("A" & i).Value & " wrap increased by " & _
Format(Sheet1.Range("C" & i).Value - Sheet1.Range("B" & i).Value, "hh:mm:ss")
Else
Wrapmessage = Wrapmessage & vbLf & Sheet1.Range("A" & i).Value & " wrap increased by " & _
Format(Sheet1.Range("C" & i).Value - Sheet1.Range("B" & i).Value, "hh:mm:ss")
End If
End If
Next
For i = 2 To 5
If Sheet1.Range("C" & i).Value < Sheet1.Range("B" & i).Value Then
If Len(Wrapmessage) = 0 Then
Wrapmessage = Sheet1.Range("A" & i).Value & " wrap decreased by " & _
Format(Sheet1.Range("C" & i).Value - Sheet1.Range("B" & i).Value, "hh:mm:ss")
Else
Wrapmessage = Wrapmessage & vbLf & Sheet1.Range("A" & i).Value & " wrap decreased by " & _
Format(Sheet1.Range("C" & i).Value - Sheet1.Range("B" & i).Value, "hh:mm:ss")
End If
End If
Next
For i = 2 To 5
If Sheet1.Range("C" & i).Value = Sheet1.Range("B" & i).Value Then
If Len(Wrapmessage) = 0 Then
Wrapmessage = Sheet1.Range("A" & i).Value & " wrap didn't change "
Else
Wrapmessage = Wrapmessage & vbLf & Sheet1.Range("A" & i).Value & " wrap didn't change "
End If
End If
Next
Exit Do
Loop
If Len(Wrapmessage) > 0 Then
On Error Resume Next
With OutMail
.Display
.To = "Michael.Mulholland@email.co.uk"
.CC = ""
.BCC = ""
.Subject = "Stats Increase"
.Body = Wrapmessage
.Send
End With
End If
End Sub
What i want to the code to do is miss out when column A is blank and then continue until it has reached the end.
Just now it is returning this:
Agent 1 wrap increased by 00:03:20
Agent 2 wrap decreased by 00:03:20
Agent 3 wrap didn't change
wrap didn't change - i don't want this to be here as column A5 has no text.
Last edited by a moderator: