Hi Sean,

Am Mon, 5 Aug 2013 10:09:00 -0700 (PDT) schrieb :

Thanks, makes sense, couple more scenario's which I've tried but can't get to work

1) If Col C has text "Opening Sequence" and Col D&E are blank, then show "Over Risk" in Col H

2) If Col C has text "GLS" and also contains a 6 numeric value & an 8 numeric value in the cell + Col D is <0 + Col E is blank, then show "Duty Exceeded" in Col H

try:
Sub Test()
Dim c As Range
Dim LRow As Long
Dim i As Integer
Dim myArr As Variant
Dim myStr As String
Dim firstaddress As String

myArr = Array("ABC", "Credit Transfer", "BOD", "Opening Sequence",
"GLS")
LRow = Cells(Rows.Count, 3).End(xlUp).Row
For i = LBound(myArr) To UBound(myArr)
Set c = Range("C1:C" & LRow).Find(myArr(i), _
LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Select Case myArr(i)
Case "ABC"
myStr = IIf(Len(c.Offset(, 1)) = 0 _
And c.Offset(, 2) 0, "Job Done", "")
Case "Credit Transfer"
myStr = IIf(IsNumeric(Right(c, 6)) And Len(c.Offset(, 1)) = 0 _
And c.Offset(, 2) 0, "Job Not Done", "")
Case "BOD"
myStr = IIf(IsNumeric(Mid(c, 5, 6)) And IsNumeric(Mid(c, 12, 8))
_
And Len(c.Offset(, 1)) = 0 And c.Offset(, 2) 0, "Job
Pending", _
"Call Back")
Case "Opening Sequence"
myStr = IIf(Len(c.Offset(, 1)) = 0 And _
Len(c.Offset(, 2)) = 0, "Over Risk", "")
Case "GLS"
myStr = IIf(IsNumeric(Mid(c, 5, 6)) And IsNumeric(Mid(c, 12, 8))
And _
c.Offset(, 1) < 0 And Len(c.Offset(, 2)) = 0, "Duty Exceeded",
"")
End Select
c.Offset(, 5) = myStr
myStr = ""
Set c = Range("C1:C" & LRow).FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
Next
End Sub

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2