Thread
:
Loop Through Text and Test for Scenarios Q
View Single Post
#
9
Posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
Posts: 3,872
Loop Through Text and Test for Scenarios Q
Hi Sean,
Am Mon, 5 Aug 2013 08:35:01 -0700 (PDT) schrieb
:
Test if:- There is the text "BOD" in Col C + there is also 6 numeric values & 8 numeric values also in Col C (can be any numeric value) + the value in Col D is blank but the value in Col E is 0 then place the text "Job Pending" in Col H
now I understand
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")
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
If Len(c.Offset(, 1)) = 0 And c.Offset(, 2) 0 Then
Select Case myArr(i)
Case "ABC"
myStr = "Job Done"
Case "Credit Transfer"
myStr = IIf(IsNumeric(Right(c, 5)), "Job Not Done", "")
Case "BOD"
myStr = IIf(IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)), "Job Pending", "")
End Select
c.Offset(, 5) = myStr
End If
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
Reply With Quote
Claus Busch
View Public Profile
Find all posts by Claus Busch