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

 

[mikerickson]

Try

Private Sub ComboBox1_DropButt*******()
Dim Found As Range, FirstFound As String, AllRows As Range
[COLOR="Red"]Dim keySheet as Worksheet
Set keySheet = ThisWorkbook.Sheets("searchingSheet")[/COLOR]

If ComboBox1.Value <> vbNullString Then

    Set Found = [COLOR="red"]keySheet[/COLOR].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 = [COLOR="red"]KeySheeet[/COLOR].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]

Thanks, that actually clears up a fudemental question I have while learning. I tried adding ---Sheets("system").Activate-- above the set found=... and it worked.

Would you mind helping with one more question? If so, how could I add an error message to the first portion of the code?

 

[Tcarey]

Never mind your method is working better.

I added another intro tab and it was pulling that sheet up as the active instead of "system"

 

[Tcarey]

When construction is selected from the list I'd like for the find function to return only contruction from B:B. However, it is looking C:E and returning anything with construction in it.

 

[Tcarey]

This is a two fold issue for me. First, I cannot figure out how to only "search" in a specific column and return only those matchs. Second, I cannot create another combobox on the same userform with out receiving the ambgious name errorr. Not sure how to get around either of these issues.

'NAICS Sector Combobox
Sub userform_Initialize()
ComboBox1.List = Array("Agriculture, Forestry, Fishing and Hunting", "Mining, Quarrying, and Oil and Gas Extraction")
End Sub
'NAICS Sector Enter button
Private Sub CommandButton2_Click()
    Dim Found As Range, FirstFound As String, AllRows As Range
    Dim keysheet As Worksheet
    Set keysheet = ThisWorkbook.Sheets("system")
    
    If ComboBox1.Value <> vbNullString Then
        Set Found = keysheet.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 = 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]

The .Find method (and .FindNext) works on the Range to which is is applied.

So to search in column B, you would use

keySheet.Range("B:B").Find...

The reason that activating the sheet worked is that an unqualified range, like Range("B:B") refers to the ActiveSheet.

It's a good practice to fully qualify ranges specifying Workbook and Worksheet, so that you don't have to keep activating to adjust the unqualified ranges to ActiveSheet.

 

[Tcarey]

This addition is not only searching the C:C range. Where am I going wrong?

'NAICS Sector Enter button
Private Sub CommandButton2_Click()
    Dim Found As Range, FirstFound As String, AllRows As Range
    Dim keysheet As Worksheet
    Set keysheet = ThisWorkbook.Sheets("system")
 
    If ComboBox1.Value <> vbNullString Then
        Set Found = [B]keysheet.Range("C:C").[/B]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 = 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]

You also have to apply it to the .FindNext

Do
    Set Found = keysheet.Range("C:C").Cells.FindNext(Found)
'...

 

[Tcarey]

Thanks for your patience.