myFind Range not working

[Darren Smith]

Please can someone say why this myFindRng goes red in the code?

Private Sub Model_Chose_Change()

With ThisWorkbook.Worksheets("Job Card Master")

Me.Quote_Details.ListIndex = Me.Model_Chose.ListIndex
myValToFind = Make_Chose.Value
Set mySearchRng = .Columns("A")
End With

Set myFindRng = mySearchRng.Find(What:=myValToFind, _
           LookIn:=xlFormulas, _
           LookAt:=xlWhole, _
           SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
           MatchCase:=False)

 

[Darren Smith]

Here you go
Sorry to be thick

[I]Private Sub Model_Chose_Change()

Dim ws          As Worksheet
Dim myFindRng   As Range, mySearchRng As Range
Dim myValToFind As Variant

Set ws = ThisWorkbook.Worksheets("Quote Detail")

With ws

Me.Quote_Details.ListIndex = Me.Model_Chose.ListIndex
myValToFind = Model_Chose.Value
Set mySearchRng = .Columns("A")
End With

Set myFindRng = mySearchRng.Find(What:=myValToFind, _
           LookIn:=xlFormulas, _
           LookAt:=xlWhole, _
           SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
           MatchCase:=False)
           
           Quote_Details.MatchEntry
           With Me.Quote_Details
                 .List(.ListCount - 1, 0) = myFindRng.Value
                 .List(.ListCount - 1, 1) = myFindRng.Offset(0, 2).Value
                 .List(.ListCount - 1, 2) = myFindRng.Offset(0, 3).Value
                 .List(.ListCount - 1, 3) = myFindRng.Offset(0, 4).Value
                 .List(.ListCount - 1, 4) = myFindRng.Offset(0, 5).Value
                 .List(.ListCount - 1, 5) = myFindRng.Offset(0, 6).Value
                 .List(.ListCount - 1, 6) = myFindRng.Offset(0, 7).Value
                 .List(.ListCount - 1, 7) = myFindRng.Offset(0, 8).Value
                 .List(.ListCount - 1, 8) = myFindRng.Offset(0, 9).Value
                 .List(.ListCount - 1, 9) = myFindRng.Offset(0, 10).Value
                 
                End With
                

End Sub[/I]

 

[RoryA]

You should always check that the value was found before you try to use the found cell:

         If myFindRng Is Nothing then
               msgbox myvaltofind & " was not found", vbokonly
        else
           'Quote_Details.MatchEntry <- this line doesn't do anything
           With Me.Quote_Details
                 .List(.ListCount - 1, 0) = myFindRng.Value
                 .List(.ListCount - 1, 1) = myFindRng.Offset(0, 2).Value
                 .List(.ListCount - 1, 2) = myFindRng.Offset(0, 3).Value
                 .List(.ListCount - 1, 3) = myFindRng.Offset(0, 4).Value
                 .List(.ListCount - 1, 4) = myFindRng.Offset(0, 5).Value
                 .List(.ListCount - 1, 5) = myFindRng.Offset(0, 6).Value
                 .List(.ListCount - 1, 6) = myFindRng.Offset(0, 7).Value
                 .List(.ListCount - 1, 7) = myFindRng.Offset(0, 8).Value
                 .List(.ListCount - 1, 8) = myFindRng.Offset(0, 9).Value
                 .List(.ListCount - 1, 9) = myFindRng.Offset(0, 10).Value
                 
                End With
            end if

 

[Darren Smith]

It`s still not filtering?
There is a Vehicle column (Column B in Quote Detail Sheet) so we need to select all the vehicles of a certain Model then filter the ListBox.

 

[RoryA]

I don't see anything that looks like filtering in that code. All it appears to do is overwrite the last item in the list with the details from the found value.

 

[Darren Smith]

Please can you show how I can filter the listbox using the combo box?

 

[RoryA]

It looks like (not for the first time) you've randomly changed parts of your code. This line:

Quote_Details.MatchEntry

should have stayed as it was originally:

Quote_Details.AddItem

 

[Darren Smith]

I have changed it back to Quote_Details.AddItem but it`s still failing to work.
The reason why I changed it was the first time I tried to use Quote_Details.AddItem it failed so I was trying to sort it out.

 

[RoryA]

You need to give us more information. Would you take your car to a garage, point at it and say "it doesn't work" and expect them to be able to diagnose it?

 

[Darren Smith]

I am trying to filter the Listbox with a combo box value with this code.
It`s taking the data from a worksheet adding it to a Listbox then I am using a combo box to filter the data in the Listbox.
The Worksheet with the data that is in Worksheet "Quote Details" column B.
Then the data for the Combo box is in the UserForm_Initialize code.
When it pick my value from the Combo box List it needs to filter the Listbox.

Is that clear?

 

[RoryA]

What I want to know is what is happening currently? "Failing to work" doesn't really tell me anything.