?
Solved

easy, but its miffing me off...

Posted on 2003-03-06
7
Medium Priority
?
160 Views
Last Modified: 2010-05-01
Hiya everybody, can anybody please take a look at the following code:

Public Sub gotlinecheck()
   
    Dim compos As Integer
    Dim extracted As String
    Dim vStrPos As Variant
    Dim found As Boolean
       
    For itrack = 0 To Form1.lst_unknown_file_contents1.ListCount - 1
               
        found = False
       
        For i2 = 0 To Form1.lst_file_contents.ListCount - 1
           
            If found = False Then
           
                extracted = Form1.lst_file_contents.List(i2)
                compos = InStr(extracted, ",") - 1
               
                If compos > 0 Then
               
                    extracted = Left(extracted, compos)
                    If Not Len(extracted) = 0 Then
                        If InStr(Form1.lst_unknown_file_contents1.List(i2), extracted) > 0 Then
                            found = True
                        Else
                            found = False
                        End If
                    End If
               
                End If
               
            End If
           
        Next i2
       
        If found = False Then
            Form1.List1.AddItem (Form1.lst_unknown_file_contents1.List(itrack))
        End If
       
    Next itrack
       
End Sub



lst_unknown_file_contents1 is a list box with about 50,000 entries in it,

lst_file_contents is a list box with about 100 entries in it.

List1 is just a list box.


The idea of this function is to loop through every entry in the lst_unknown_file_contents1 and inside each iteration it loops through lst_file_contents. At the end of the first iteration it checks if the string from lst_unknown_file_contents1 was found in the lst_file_contents. If it wasn't then it adds it to list1, otherwise it doesn't. However the problem I'm getting is that:


If found = False Then


This seems to cause a problem, when I leave it like that I get an "out of context" error with itrack. However when its changed to:

If found = True Then


It works ok, except from the fact that it then adds everything that was found, which isn't what I'm wanting, as there is most likely 90% of the entries in lst_unknown_file_contents1 that are not in the lst_file_contents list box.


Can anybody help please?
Thanks
Uni
0
Comment
Question by:Unimatrix_001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 11

Expert Comment

by:supunr
ID: 8085266
Public Sub gotlinecheck()
 
   Dim compos As Integer
   Dim extracted As String
   Dim vStrPos As Variant
   Dim found As Boolean
       
   For itrack = 0 To Form1.lst_unknown_file_contents1.ListCount - 1
               
       found = False
       
       For i2 = 0 To Form1.lst_file_contents.ListCount - 1
           
               extracted = Form1.lst_file_contents.List(i2)
               compos = InStr(extracted, ",") - 1
               
               If compos > 0 Then
               
                   extracted = Trim(Left(extracted, compos)) ' <- supunr
                   If Len(extracted) > 0 Then     ' <- supunr
                       If InStr(Form1.lst_unknown_file_contents1.List(i2), extracted) > 0 Then
                           found = True
                  Exit For       ' <- supunr
                       Else
                           found = False
                       End If
                   End If
               
               End If
           
       Next i2
       
       If found = False Then
           Form1.List1.AddItem (Form1.lst_unknown_file_contents1.List(itrack))
       End If
       
   Next itrack
       
End Sub
0
 
LVL 11

Expert Comment

by:supunr
ID: 8085272
Public Sub gotlinecheck()
 
   Dim compos As Integer
   Dim extracted As String
   Dim vStrPos As Variant
   Dim found As Boolean
       
   For itrack = 0 To Form1.lst_unknown_file_contents1.ListCount - 1
               
       found = False
       
       For i2 = 0 To Form1.lst_file_contents.ListCount - 1
           
               extracted = Form1.lst_file_contents.List(i2)
               compos = InStr(extracted, ",") - 1
               
               If compos > 0 Then
               
                   extracted = Trim(Left(extracted, compos)) ' <- supunr
                   If Len(extracted) > 0 Then     ' <- supunr
                       If InStr(Form1.lst_file_contents.List(i2), extracted) > 0 Then ' <- supunr
                           found = True
                  Exit For       ' <- supunr
                       Else
                           found = False
                       End If
                   End If
               
               End If
           
       Next i2
       
       If found = False Then
           Form1.List1.AddItem (Form1.lst_unknown_file_contents1.List(itrack))
       End If
       
   Next itrack
       
End Sub
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 8085344
no where in your code are you comparing the extracted found string to the test string!

your
instr(form1.lst_file_contents(i2),extracted)>0 then

should use index itrack, not i2
Put an "Exit for" between found=true and else to break out of the loop when you've made your case for having to add the element.

I've just tested the code using the datasets below, inferring the data form from your code:

apples
oranges
pears
peaches
grapes
        and
oranges,blah
yellow,blah
grapes,blah
red,blah


it adds apples,pears, and peaches to the list box.
-----
Its a pretty complicated nest you've got there.  If you do indeed have 50,000 entries, may I recommend that you create a variables to store the values of Form1.lst_unknown_file_contents1.ListCount-1, Form1.lst_file_contents.ListCount - 1
and form1.lst_file_contents(itrack)

The fewer references to controls or functions in loops, the faster it will be, and setting a variable "teststring = form1.lst_file_contents(itrack)" in the outer loop for your instr(test codition) line will avoid having to compute this string for each and every comparison...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
GPrentice00 earned 160 total points
ID: 8085449
A few modifications in line with the recommendations to trim the code -- cut down on control/property/calculation usages in the loop, and elimination of some IF/else/endif conditions.

Gives the exact same result on my dataset.

There's probably a few other tweaks yet that can be made, esp a grand simplification of the testing strings: You are extracting a comma denoted string "bananas" from "bananas,blah", then comparing that to the other test string.  Thats a lot of extra work to do, when instr will find "bananas" in "bananas,blah" or not find it it "cherries,blah".....



Public Sub gotlinecheck()

   Dim compos As Integer
   Dim extracted As String
   Dim vStrPos As Variant
   Dim found As Boolean
   Dim itrack As Integer
   Dim i2 As Integer
   Dim unk_file_countm1 As Integer
   Dim file_con_countm1 As Integer
   Dim teststring As String
   
   unk_file_countm1 = Form1.lst_unknown_file_contents1.ListCount - 1
   file_con_countm1 = Form1.lst_file_contents.ListCount - 1
   
   For itrack = 0 To unk_file_countm1
      found = False
      teststring = Form1.lst_unknown_file_contents1.List(itrack)
      For i2 = 0 To Form1.lst_file_contents.ListCount - 1
         extracted = Form1.lst_file_contents.List(i2)
         compos = InStr(extracted, ",") - 1
         If compos > 0 Then
            extracted = Left(extracted, compos)
            If Not Len(extracted) = 0 Then
               If InStr(teststring, extracted) > 0 Then
                  found = True        ' false turns true only in this case
                  Exit For
               End If
            End If
         End If
      Next i2     ' next will continue until end (found=false), or until found=true
      If Not found Then
         Form1.List1.AddItem (teststring)
      End If
   Next itrack

End Sub
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 8085495
Here's the code <segment> without the extra string manipulations that produces the same results with my testdata


   unk_file_countm1 = Form1.lst_unknown_file_contents1.ListCount - 1
   file_con_countm1 = Form1.lst_file_contents.ListCount - 1
   
   For itrack = 0 To unk_file_countm1
      found = False
      teststring = Form1.lst_unknown_file_contents1.List(itrack)
      For i2 = 0 To Form1.lst_file_contents.ListCount - 1
         
          If InStr(Form1.lst_file_contents.List(i2), teststring) > 0 Then
            found = True
            Exit For
          End If
      Next i2     ' next will continue until end (found=false), or until found=true
      If Not found Then
         Form1.List1.AddItem (teststring)
      End If
   Next itrack
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 8085536
oops -- forgot to actually replace the loop i2 endpoint with the variable in the codes.  

For i2 = 0 To file_con_countm1


You should find that with 50,000 list items and several hundred in the other, thats a lot of checking loops, you'll be waiting a lot less time for results now.

0
 
LVL 3

Author Comment

by:Unimatrix_001
ID: 8088868
You are fantastic! :D Thanks very much, if I had any more points I'd gladly hand them over! :) :)

Thanks again,
Uni
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question