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)

 

[RoryA]

Where and how is myFindRng declared?

 

[Darren Smith]

See the whole code below

Private Sub Model_Chose_Change()

Dim ws        As Worksheet
Dim RngData   As Range
Dim myFindRng As Range

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

With Me.Quote_Details
    .ColumnHeads = True
    .ColumnCount = RngData.Columns.Count
    
    Set RngData = RngData.Resize(RngData.Rows.Count - 1).Offset(1)
    .ColumnWidths = "90;60;100;150;90;80;100;95;60"
    
 End With
 
With ws


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)
           
           Me.Quote_Details.AddItem
           With Me.Quote_Details
                 .List(.ListCount - 1, 0) = myFindRng.Value
                 .List(.ListCount - 1, 1) = MyFingRng.Offset(0, 2).Value
                 .List(.ListCount - 1, 2) = MyFingRng.Offset(0, 3).Value
                 .List(.ListCount - 1, 3) = MyFingRng.Offset(0, 4).Value
                 .List(.ListCount - 1, 4) = MyFingRng.Offset(0, 5).Value
                 .List(.ListCount - 1, 5) = MyFingRng.Offset(0, 6).Value
                 .List(.ListCount - 1, 6) = MyFingRng.Offset(0, 7).Value
                 .List(.ListCount - 1, 7) = MyFingRng.Offset(0, 8).Value
                 .List(.ListCount - 1, 8) = MyFingRng.Offset(0, 9).Value
                 .List(.ListCount - 1, 9) = MyFingRng.Offset(0, 10).Value
                 
                End With
End Sub

 

[NoSparks]

SearchOrder has the equal sign in the wrong place

 

[RoryA]

Which is weird because it doesn't in the original post.

 

[Darren Smith]

I am trying to filter the Listbox with a combo box value with this code.
The part below I am struggling with it just gets stuck at this line. Says Run-Time error `70` Permission denied??

Quote_Details.AddItem

The forms fill-in off the Userform Initialize Code.

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.AddItem
           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
                 Add
                End With
                

End Sub

 

[RoryA]

Is your listbox bound to a range using RowSource? If so, you can't use AddItem to add items to it. You have to update the source range.

 

[Darren Smith]

No it`s not bound to a range using row source

 

[RoryA]

How is Quote_Details populated then? I've never seen that error caused by anything else.

 

[Darren Smith]

From a worksheet to a Listbox