Loop Through Text and Test for Scenarios Q

  • Thread starter Thread starter seanryanie
  • Start date Start date
Below is the only part I am having issues with, it shows as <blank> in Col H


myStr = IIf(myArr(i) = "GLS" And IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)), "Duty Exceeded", "")

The actual value in Col C for above would look like "GLS 123456 78901234" and it would have <0 in Col D and 0 in Col E
 
Hi Sean,

Am Mon, 5 Aug 2013 11:55:31 -0700 (PDT) schrieb (e-mail address removed):
Below is the only part I am having issues with, it shows as <blank> in Col H

myStr = IIf(myArr(i) = "GLS" And IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)), "Duty Exceeded", "")
Click to expand...

sorry, my bad.

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", "XYZ")
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, 6)), _
"Job Not Done", "")
Case "BOD"
myStr = IIf(IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)), "Job Pending", "Call Back")
End Select
End If
If Len(c.Offset(, 1) & c.Offset(, 2)) = 0 Then
myStr = IIf(myArr(i) = "Opening Sequence", "Over Risk", "")
End If
If c.Offset(, 1) < 0 And Len(c.Offset(, 2)) = 0 Then
If myArr(i) = "GLS" And IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)) Then
myStr = "Duty Exceeded"
ElseIf myArr(i) = "XYZ" Then
myStr = "Top Level"
End If
End If
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.
 
Bingo, that works great Claus, thanks for your help. Might have a few more scenarios to add, but thats it for today
 
One final one tonight!, below checks if "Credit Transfer" Appears in ColC and ALSO there is a value with 6 numerics, correct? If so can this be amended so that.....

"Credit Transfer" Appears in ColC and ALSO there is a value between 4 & 6 numerics

Case "Credit Transfer"
myStr = IIf(IsNumeric(Right(c, 6)), "Notes Lodged", "")
 
Hi Sean,

Am Mon, 5 Aug 2013 12:28:34 -0700 (PDT) schrieb (e-mail address removed):
Case "Credit Transfer"
myStr = IIf(IsNumeric(Right(c, 6)), "Notes Lodged", "")
Click to expand...

try:
Case "Credit Transfer"
If Len(c) = 21 And IsNumeric(Right(c, 5)) Then
myStr = "Job Not Done"
ElseIf Len(c) = 22 And IsNumeric(Right(c, 6)) Then
myStr = "Notes Lodged"
End If


Regards
Claus B.
 
Claus could you explain what below is doing? I'm assuming its counting the characters in the cell

If Len(c) = 21 And IsNumeric(Right(c, 5)) Then

myStr = "Job Not Done"
 
Hi Sean,

Am Mon, 5 Aug 2013 13:06:41 -0700 (PDT) schrieb (e-mail address removed):
Claus could you explain what below is doing? I'm assuming its counting the characters in the cell

If Len(c) = 21 And IsNumeric(Right(c, 5)) Then
Click to expand...

yes, it is counting the characters. Because if right(c,6) is numeric
right(c,5) is numeric too. So I looked for a difference and took the
length of the cell.
Is it not working correctly?


Regards
Claus B.
 
No, ok, forget that one, a new scenario that will cover this off

If Col C contains the text "Skydrive" AND it will also contain a numeric value ranging from between 3 and 5 digits
 
Am Mon, 5 Aug 2013 13:17:04 -0700 (PDT) schrieb (e-mail address removed):
No, ok, forget that one, a new scenario that will cover this off

If Col C contains the text "Skydrive" AND it will also contain a numeric value ranging from between 3 and 5 digits
Click to expand...

and what is in D and E? And what should be write in H?


Regards
Claus B.
 
Hi again,

Am Mon, 5 Aug 2013 13:26:56 -0700 (PDT) schrieb (e-mail address removed):
Sorry didn't complete it, Col D is <blank> and ColE >0, in COL H will be "Dropbox"
Click to expand...

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", "XYZ", "Skydrive")
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"
If Len(c) = 21 And IsNumeric(Right(c, 5)) Then
myStr = "Job Not Done"
ElseIf Len(c) = 22 And IsNumeric(Right(c, 6)) Then
myStr = "Notes Lodged"
End If
Case "BOD"
myStr = IIf(IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)), "Job Pending", "Call Back")
Case "Skydrive"
myStr = IIf(Len(c) >= 12 And Len(c) <= 14 And _
IsNumeric(Trim(Replace(c, "Skydrive", ""))), "DropBox", "")
End Select
End If
If Len(c.Offset(, 1) & c.Offset(, 2)) = 0 Then
myStr = IIf(myArr(i) = "Opening Sequence", "Over Risk", "")
End If
If c.Offset(, 1) < 0 And Len(c.Offset(, 2)) = 0 Then
If myArr(i) = "GLS" And IsNumeric(Mid(c, 5, 6)) And _
IsNumeric(Mid(c, 12, 8)) Then
myStr = "Duty Exceeded"
ElseIf myArr(i) = "XYZ" Then
myStr = "Top Level"
End If
End If
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.
 
Back
Top