Need to nest .Find routines in VBA

[Moph]

Hi guys

I'm creating an orders management system (OMS) for an sole practitioner engineering consultancy I'll be setting up mid next year. The main 'database' (Excel sheet with entries in rows) contains the order number, site details etc but for various reasons the client, client rep and site owner are in separate databases on separate sheets and are referenced in the main database by client number etc.

I want to be able to search my order history by company, for example. This means that the search routine needs to search the client 'database', locate and remember the client reference number, search the main 'database' for any orders with that reference number, and output the order number from the main database to the search page. It then needs to move back to the client page and continue searching the database in case I have entered a partial company name and there are other matching entries.

Hope that makes sense... :-/

My sub-routine code so far is:

      Set c = SRangeRef.Find("*" & SString & "*", lookat:=xlWhole, MatchCase:=False)
      If Not c Is Nothing Then
         cfirstaddress = c.Address
         RefNo = SSheet.Cells(c.Row, 1)
         Do
            Set d = SRangeData.Find(RefNo, lookat:=xlWhole, MatchCase:=False)
            If Not d Is Nothing Then
               dfirstaddress = d.Address
               Do
                  Set OrderNo = Worksheets("Orders").Cells(d.Row, 1)
                  Cells(Counter, 1).Value = Worksheets("Orders").Cells(d.Row, 1)    
                  Counter = Counter + 1
                  Set d = SRangeData.FindNext(d)
               Loop While Not d Is Nothing And d.Address <> dfirstaddress
            End If
         MsgBox SRangeRef.FindNext(c).Address
         Set c = SRangeRef.FindNext(c)
         MsgBox c
         Loop While Not c Is Nothing And c.Address <> cfirstaddress
      End If

In the above variables:
- SRange is search range
- SString is search string
- Ref refers to the sub-database (client / client rep / owner)
- Data refers to the main "Orders" database
- RefNo is the client / client rep / owner reference number found in the corresponding sub-database
- OrderNo is the final output of the search

I'm able to get the search routine to perform an un-nested search no worries. Enter a partial company name for example and it will give me a list from the client database of all company reference numbers matching that partial string.

As soon as I nest one .Find within the other all goes crazy though The inner .Find routine works but I just can't find a way to get the outer loop to work.

Had it programmed as With ... End With earlier but took them out for troubleshooting. My VBA skills are monkey see, monkey copy, monkey modify, very lucky monkey do!

Cheers for any help!
- Moph

[Bob Phillips]

How about dropping it into an Access database Moph, and using SQL to do the query?

[Moph]

How about dropping it into an Access database Moh, and using SQL to do the query?

Bit out of my depth there, xld

I did have a play with Access but didn't get far before I slunk back to the somewhat familiar Excel / VBA environment. Haven't got a clue how do SQL queries and am most of the way there with the Excel/VBA setup - front end is up and pretty, works well. Just this one issue with the search facility is holding me back.

It won't be a massive database - likely only 300 to 500 orders per year absolute tops - so Excel should be able to handle it. I filled the database with 65,000 'junk' entries and the longest any of my searches took was about 4 secs, so its fine for my intended use.

[Bob Phillips]

OK then, can you post a workbook to work with?

[Moph]

Okay - workbook attached. I've removed the front end to get the attachment size down (file size was up to 4MB) so all that is there is the uncompleted Search interface and the background database tabs with some junk data.

There are some notes at the top of the Search tab explaining how to use it. Searches in the main database alone work fine (although I did note that searching for say a single character 'o' will return multiple matches in the one cell, which I should fix) - searches that need to look up (say) client reference number and then search client reference number in the main database don't.

Cheers =)