Moph
10-20-2009, 10:25 AM
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
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