Loop Through Text and Test for Scenarios Q

S

seanryanie

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
 
C

Claus Busch

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.
 
S

seanryanie

Bingo, that works great Claus, thanks for your help. Might have a few more scenarios to add, but thats it for today
 
S

seanryanie

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", "")
 
C

Claus Busch

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.
 
S

seanryanie

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"
 
C

Claus Busch

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.
 
S

seanryanie

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
 
C

Claus Busch

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.
 
S

seanryanie

Sorry didn't complete it, Col D is <blank> and ColE >0, in COL H will be "Dropbox"
 
C

Claus Busch

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide / Unhide each row from StartRow to LastRow for multiple conditions 6
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
Column number of last column that returns value ignore formulas 8
Named Range maker code 9
Loop thru xx No. of sheets until the four strValue combo's are found 6
Advanced transpose/grouping question 7
Multiple Criteria for worksheet 3
Siva and surya 2

Top