myFind Range not working

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Please can someone say why this myFindRng goes red in the code?

VBA 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)
 
Last edited:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Where and how is myFindRng declared?
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
See the whole code below

VBA Code:
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

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
SearchOrder has the equal sign in the wrong place
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
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??
VBA Code:
Quote_Details.AddItem
The forms fill-in off the Userform Initialize Code.
VBA 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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How is Quote_Details populated then? I've never seen that error caused by anything else.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,000
Messages
5,622,120
Members
415,878
Latest member
jjj12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top