1. ## [RESOLVED] Tricky requirement

In my worksheet in column 'C' I have sets of 11 numbers line these

8011
2485
4017
2477
4461
8057
4883
0399
2405
2457
8157

and using VBA I need to know if the numbers 1 and 7 occur twice (or more). They do, the first time in 4017 and the second time in 8157. How can I do that?  Reply With Quote

2. ## Re: Tricky requirement

One way is to use a combination of mod 10 to extract the last digit and integer division by 10 to keep moving the imaginary decimal point to the left.

Here is a simple example in VB6, converting it to VBA should be trivial.

Code:
```Private Sub Command1_Click()

Dim a As Long
Dim b As Long
Dim found1 As Boolean
Dim found7 As Boolean
a = CLng(Text1.Text)

Do While a > 0
b = a Mod 10
If b = 1 Then
found1 = True
ElseIf b = 7 Then
found7 = True
End If
a = a \ 10
Loop
If found1 = True And found7 = True Then
MsgBox "found both"
End If

End Sub```  Reply With Quote

3. ## Re: Tricky requirement

Another way is to simply treat the cell value as a String and use Instr to check for the presence of both 1 and 7. Again, below is a VB6 example, VBA conversion should be trivial.

Code:
```Private Sub Command2_Click()

If InStr(1, Text1.Text, "1") > 0 And InStr(1, Text1.Text, "7") > 0 Then
MsgBox "found both"
End If

End Sub```  Reply With Quote

4. ## Re: Tricky requirement

Thanks. You missed the requirement that 1 and 7 be found twice and not just that the string contains both. I can modify your second example to do that but I was hoping for a solution that included stepping through each of the 11 values.  Reply With Quote

5. ## Re: Tricky requirement Originally Posted by MartinLiss Thanks. You missed the requirement that 1 and 7 be found twice and not just that the string contains both. I can modify your second example to do that but I was hoping for a solution that included stepping through each of the 11 values.
I read that part and it wasn't (and still isn't) exactly clear to me what you meant by that.  Reply With Quote

6. ## Re: Tricky requirement

Pseudo-code:
Code:
```counter = 0
for each item in column C
if item contains 1 and item contains 7 then counter++
next
if counter > 1 then
print "1 and 7 pairs occur {counter} times"
end if```
My VB6 skills are too rusty now so there is some ugly code:
VB6 Code:
```Dim vars() As String
vars = Split("8011,2485,4017,2477,4461,8057,4883,0399,2405,2457,8157", ",")
Dim cnt As Integer
cnt = 0
Dim item As String
Dim vitem As Variant
For Each vitem In vars
item = vitem
If InStr(item, "1") > 0 And InStr(item, "7") Then
cnt = cnt + 1
End If
Next
If cnt > 1 Then
Debug.Print "1 and 7 pairs are found more than once. Count=" & cnt
End If```  Reply With Quote

7. ## Re: Tricky requirement

Thanks peterst your VB6 code works in VBA.  Reply With Quote

8. ## Re: [RESOLVED] Tricky requirement

This one (Like-Operator) respects the order, that "1" has to be before "7" (anywhere in the source)

Code:
```Sub main()
Dim vars() As String
vars = Split("8011,2485,4017,2477,4461,8057,4883,0399,2405,2457,8157", ",")

Dim cnt As Long
cnt = 0

For i = LBound(vars) To UBound(vars)
If vars(i) Like "*1*7*" Then cnt = cnt + 1
Next

If cnt > 1 Then Debug.Print "Yes"

End Sub```  Reply With Quote

9. ## Re: [RESOLVED] Tricky requirement

Thanks Zvoni but that's not necessary for my purposes.  Reply With Quote

10. ## Re: [RESOLVED] Tricky requirement

Ah, OK.

I implied from your description and sample data  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•