Find method loop problem

Hi

I have the following code to find the first occurance of a selected date (txtWeekCommencing)then check if that date corresponds to a selected department (Depot) then loop until both criteria are met.

The code is reading the selected date but not working to check the department.

What do you think is going wrong?

Thanks

Please Login or Register  to view this content.

I should have added that there are similar dates for other departments but I want to search for a date AND a department.

Any advice will be very welcome!

I want to search for a date AND a department.
Need to set-up two find statements. If the first find is true then you can set up a second find.
What is Depot?

Or put an if condition once the find returns the first match.

Thanks for your response AB33. I am new to VBA and have adapted the code unsuccessfully. Could you expand on your suggestion and advise where I should put the other find statement?

Thanks

Thanks for that advice, Holger. I will try that out.

Hi again Holger

I am still struggling with this. I have a sheet called Bookings Form which holds dates in column A and associated data in columns C, D, E, F, and G. In column G is the department name (variable Depot).

There could be several rows of data for a date and Depot. Also there could be several rows of data for another Depot for the same date. I was looking for the code to pick up all the data for a specific Depot for the selected date.

The code I have written picks up the first Depot but not any other selected Depot. It's so frustrating as I am not particularly familiar with arrays so cannot see where I am going wrong.

Good morning Holger

Thanks for coming back.

The Module is very large and contains confidential information so will be difficult to send in open forums.

The code below preceeds the function call:

Please Login or Register  to view this content.

The variable Depot is set here and the date is also set just before this.

The Depot is set when user clicks a command button with that Depot name. The UDF works for the first time the date and the first named Depot is found. I have slightly changed the original UDF to:

Please Login or Register  to view this content.

However when another Depot button is clicked, the UDF finds the date but as the rngfnd is the first Depot found the code goes out at this line:

Please Login or Register  to view this content.

The code in the UDF is not finding another Depot.

I hope this explains my problem?

Cheers

Good morning Holger

Thanks for coming back.

The Module is very large and contains confidential information so will be difficult to send in open forums.

The code below preceeds the function call:

Please Login or Register  to view this content.

The variable Depot is set here and the date is also set just before this.

The Depot is set when user clicks a command button with that Depot name. The UDF works for the first time the date and the first named Depot is found. I have slightly changed the original UDF to:

Please Login or Register  to view this content.

However when another Depot button is clicked, the UDF finds the date but as the rngfnd is the first Depot found the code goes out at this line:

Please Login or Register  to view this content.

The code in the UDF is not finding another Depot.

I hope this explains my problem?

Cheers

sorry I clicked the send reply twice!

PHP Code: 

'Option Explicit'
Private Function m_DoFind(Data As Range) As Long
' Find all occurances and store addresses in array'
 Dim RngFind As Range, Cls As Range, Rng As Range, Rg0 As Range
 Dim strFirstAddress As String
 
 m_DoFind = 0    ' No finds'
 ReDim m_strFindAddresses(m_DoFind) As String
 'run the search'
 With Data
    Set RngFind = .Find(what:=txtWeekCommencing.Value, LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    'Tf the item is found the user is informed and the textbox cleared'
    If Not RngFind Is Nothing Then
        Set Rng = Range(RngFind, Data(Data.Cells.Count))
        For Each Cls In Rng
            If Cls.Offset(0, 6) = Depot Then
                If Rg0 Is Nothing Then
                    Set Rg0 = Cls
                Else
                    Set Rg0 = Union(Rg0, Cls)
                End If
            End If
        Next Cls
    End If
 End With
 m_DoFind = Rg0.Cells.Count
End Function 


Thanks Sa DQ. Your code seems to move things forward, but how do I change the output code below to read what your code finds?

Please Login or Register  to view this content.

I am getting a Subscript out of range error at line

Please Login or Register  to view this content.

Thanks