Find a value and then copying the Row that contains the value

[starchildren3317]

I am getting a type mismatch error on a function that I created and can't figure out why. Perhaps one of you can find where I am going wrong.

First some background into what I am trying to do:

I have a workbook with four worksheets. The first one I have created a form to perform the certain tasks. The other three worksheets contain data.

I am trying to look in a worksheet for a value and then copy the entire row that contains that value into the first worksheet.

Here is a snip of what I have:

Dim FindRange As Range

If chbSM.Value Then [COLOR=SeaGreen]'chbSM is the name of a checbox in my form.[/COLOR]
    FindRange = FindValue("Sample Master") [COLOR=SeaGreen]'FindValue is my function, I am passing in a value to search for. [/COLOR]  
    
    With Worksheets(2).Range(FindRange)
        .Range(.Cells(1), .End(xlToRight)).Copy Destination:=Worksheets("AppSelect").Range("A2")
    End With
    
End If

[COLOR=SeaGreen]'Here is the function[/COLOR] 

Function FindValue(FindWhat As String)

Dim FoundRange As Range

[B]Set FoundRange = Cells.Find(What:=FindWhat, After:=Range("A1").Activate, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)[/B]
        
End Function

When I run this I get a type mismatch error starting on the bolded line of code above.

 

[pboltonchina]

Try this on a copy of your data

Private Function SheetExists(Sheetname As String) As Boolean
    ' Returns TRUE if a sheet exists in the active workbook
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(Sheetname)
                If Err = 0 Then SheetExists = True _
                Else SheetExists = False
End Function

Sub FindAllSheets()
    Dim Found As Range, WS As Worksheet, LookFor As Variant
        LookFor = InputBox("Enter value to find")
            
            If LookFor = "" Then Exit Sub
            
            '   Clear or Add a Results sheet
            If SheetExists("Search Results") Then
              Sheets("Search Results").Activate
              Range("A2").Select
              Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
              Selection.ClearContents
            Else
                Sheets.Add after:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Search Results"
            End If
            
            For Each WS In ActiveWorkbook.Worksheets
                If WS.Name <> "Search Results" Then
                     Set Found = WS.Cells.Find(What:=LookFor)
                     If Found Is Nothing Then
                         Range("D5").Select
                     Else
                         Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                     End If
                End If
            Next WS
End Sub