Found Function Help please

[Tcarey]

I'm having some trouble making the below code "find" the cells unless I have the location of the data as the active sheet. I need to be on a blank page and bring the rows over onto that page from a seperate sheet.

Private Sub ComboBox1_DropButt*******()
Dim Found As Range, FirstFound As String, AllRows As Range
If ComboBox1.Value <> vbNullString Then

Set Found = ActiveSheet.Cells.Find(ComboBox1.Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Found Is Nothing Then
MsgBox "No match found.", vbCritical, "No Match"
Else

FirstFound = Found.Address
Set AllRows = Found.EntireRow

Do
Set Found = Cells.FindNext(Found)
Set AllRows = Union(AllRows, Found.EntireRow)

Loop Until Found.Address = FirstFound

AllRows.Copy
Sheets("Risk").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
End If
End If
End Sub

 

[Tcarey]

I'm still searching for the knowledge to create a second combo box with the same values but to search a different range. The error is an ambiguous name detected.
This is how I populated the first combobox. I'm not sure how to make another work without the error occuring or simply having a blank list.

Sub userform_Initialize()
ComboBox1.List = Array("Agriculture, Forestry, Fishing and Hunting", "Mining, Quarrying, and Oil and Gas Extraction")
End Sub

 

[mikerickson]

The items in that combo box are not coming from a range, they are hard-coded.

to fill a userform combobox from a range, I'd use code like

Dim oneCell as Range

With ListBox2
    For Each oneCell in ThisWorkbook.Sheet("Sheet1").Range("A1:A10")
        .AddItem CStr(oneCell.Value)
    Next oneCell
End With

 

[Tcarey]

I didn't realize sub userform_initialize() would allow you to add more than one array.

'NAICS Sector Combobox
Sub userform_Initialize()
ComboBox1.List = Array("Agriculture")
ComboBox2.List = Array("Agribusiness")
End Sub

I doubt this is the best way to search, but it is what I have right now. The issue is that if I search for oil it will return and match with oil weather it is oilseed, oil, or boiler.

'page 2 keyword search
Private Sub CommandButton4_Click()
    Dim Found As Range, FirstFound As String, AllRows As Range
    Dim keysheet As Worksheet
    Set keysheet = ThisWorkbook.Sheets("system")
    
    If TextBox2.Value <> vbNullString Then
        Set Found = keysheet.Cells.Find(TextBox2.Value, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                         
        If Found Is Nothing Then
            MsgBox "No Match Found.", vbError, "Error"
        Else
        
            FirstFound = Found.Address
            Set AllRows = Found.EntireRow
            
            Do
                Set Found = keysheet.Cells.FindNext(Found)
                Set AllRows = Union(AllRows, Found.EntireRow)
                
            Loop Until Found.Address = FirstFound
            
            AllRows.Copy
            Sheets("Risk").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
            Range("A2").Select
            
        End If
        End If
      End Sub

 

[mikerickson]

If you want "oil" to find "oil" or "Oil" but not "olive oil" then set the arguments of the .Find apropriatly.

Lookat:=xlWhole, MatchCase:= False

 

[Tcarey]

Unfortunately, there are columns with Mining, Quarrying, and Oil and Gas Extraction as the value. The xlwhole won't find this type of cell.