I'm looking to obtain code that will do the following:-

Loop through Text/Numeric values in Col C, if it meets certain criteria then place variable text in Col H

The criteria I wish to test against is multiple e.g Test if:-

the fist 3 characters in Col C are the letters ABC + the value in Col D is blank but the value in Col E is 0 then place the text "Job Done" in Col H

Example 2 might be Test if:-

There is the text "Credit Transfer" in Col C + there is also 5 numeric values 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 Not Done" in Col H

Example 3 might be 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

If I can master above, I can add further scenarios

seanryanie wrote:

I'm looking to obtain code that will do the following:-

Loop through Text/Numeric values in Col C, if it meets certain criteria
then place variable text in Col H

The criteria I wish to test against is multiple e.g Test if:-

the fist 3 characters in Col C are the letters ABC + the value in Col D
is blank but the value in Col E is 0 then place the text "Job Done" in
Col H

Example 2 might be Test if:-

There is the text "Credit Transfer" in Col C + there is also 5 numeric
values 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 Not Done" in Col H

Example 3 might be 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

If I can master above, I can add further scenarios

You need to clarify what you mean by "5 numeric values in Col C" and "6
numeric values & 8 numeric values also in Col C". Numeric values in the
current row, column C? Numeric values *anywhere* in the column? What?

Here's a start on your code. The 2 places where it says "(True)" are where
clarification is needed:

Sub dwim()
For L0 = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
If ("ABC" = Left$(Cells(L0, 3).Value, 3)) And _
("" = Cells(L0, 4).Value) And (Cells(L0, 5).Value 0) Then
Cells(L0, 8).Value = "Job Done"
ElseIf ("Credit Transfer" = Cells(L0, 3).Value) And (True) And _
("" = Cells(L0, 4).Value) And (Cells(L0, 5).Value 0) Then
Cells(L0, 8).Value = "Job Not Done"
ElseIf (InStr(Cells(L0, 3).Value, "BOD")) And (True) And _
("" = Cells(L0, 4).Value) And (Cells(L0, 5).Value 0) Then
Cells(L0, 8).Value = "Job Pending"
End If
Next
End Sub

--
It is more disgraceful to distrust than to be deceived by our friends.

Thanks for your reply. What I meant in e.g. 3 the cell contents in ColC could be "BOD 123456 12345678" <- As you can see I've 2 numeric values separated by a space, first has 6 numerics and the 2nd has 8 numerics

hi Sean,

Am Mon, 5 Aug 2013 07:42:05 -0700 (PDT) schrieb :

Thanks for your reply. What I meant in e.g. 3 the cell contents in ColC could be "BOD 123456 12345678" <- As you can see I've 2 numeric values separated by a space, first has 6 numerics and the 2nd has 8 numerics

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 = "Job Not Done"
Case "BOD"
myStr = "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

Thanks Claus, one Q, what part of your code has the numeric qualifiers I've mentioned?

Hi Sean,

Am Mon, 5 Aug 2013 08:12:46 -0700 (PDT) schrieb :

Thanks Claus, one Q, what part of your code has the numeric qualifiers I've mentioned?

the code searches in part of the cells. It don't look for numeric
values.


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

Hi Sean,

Am Mon, 5 Aug 2013 08:12:46 -0700 (PDT) schrieb :

Thanks Claus, one Q, what part of your code has the numeric qualifiers I've mentioned?

what do you mean with numeric qualifiers?
If D is empty and E greater than 0? That makes the IF-Statement when c
is not nothing.


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