[RESOLVED] Tricky requirement

[MartinLiss]

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?

[OptionBase1]

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.

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

[OptionBase1]

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.

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

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

[OptionBase1]

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.

[peterst]

Pseudo-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:

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

[MartinLiss]

Thanks peterst your VB6 code works in VBA.

[Zvoni]

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

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

[MartinLiss]

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

[Zvoni]

Ah, OK.

I implied from your description and sample data