Results 1 to 10 of 10

Thread: [RESOLVED] Tricky requirement

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,234

    Resolved [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?

  2. #2
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    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

  3. #3
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    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

  4. #4

  5. #5
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    Re: Tricky requirement

    Quote Originally Posted by MartinLiss View Post
    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.

  6. #6
    Hyperactive Member
    Join Date
    Jun 2019
    Posts
    339

    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:
    1. Dim vars() As String
    2. vars = Split("8011,2485,4017,2477,4461,8057,4883,0399,2405,2457,8157", ",")
    3.  
    4. Dim cnt As Integer
    5. cnt = 0
    6.  
    7. Dim item As String
    8. Dim vitem As Variant
    9. For Each vitem In vars
    10.     item = vitem
    11.     If InStr(item, "1") > 0 And InStr(item, "7") Then
    12.         cnt = cnt + 1
    13.     End If
    14. Next
    15.  
    16. If cnt > 1 Then
    17.     Debug.Print "1 and 7 pairs are found more than once. Count=" & cnt
    18. End If

  7. #7

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,601

    Re: [RESOLVED] Tricky requirement

    Addendum:
    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
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,601

    Re: [RESOLVED] Tricky requirement

    Ah, OK.

    I implied from your description and sample data
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

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



Click Here to Expand Forum to Full Width