Compile Error. Sub of Function not Defined.

Hi, Im really new to VBA Macro and i would appreciate all the help I can get from anyone.
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.

I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.

I've attached the spreadsheet so you get a better idea of the problem that i encountered. Thanks soo much in advance!

Sub TestFindAll()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TestFindAll
' This is a test procedure for FindAll.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim SearchRange As Range
    Dim FoundCells As Range
    Dim FoundCell As Range
    Dim FindWhat As Variant
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LookAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    
    ''''''''''''''''''''''''''
    ' Set the variables to the
    ' appropriate values.
    ''''''''''''''''''''''''''
    Set SearchRange = ThisWorkbook.Worksheets("Sheet 1").Range("F7:Q13")
  
    LookIn = xlValues
    LookAt = xlPart
    SearchOrder = xlByRows
    MatchCase = False
    
    '''''''''''''''''''
    ' Search the range.
    '''''''''''''''''''
    Set FoundCells = FindAll(SearchRange:=SearchRange, FindWhat:=FindWhat, _
        LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    
    ''''''''''''''''''''''
    ' Display the results.
    ''''''''''''''''''''''
    If FoundCells Is Nothing Then
        Debug.Print "No cells found."
    Else
        For Each FoundCell In FoundCells.Cells
            Debug.Print FoundCell.Address, FoundCell.Text
        Next FoundCell
    End If
    
End Sub

Hi

You don't have any code for the function FindAll in this workbook. What is the code for this function?

rylo

Try http://www.cpearson.com/excel/RangeFind.htm It has all the function examples for what you are doing.

Thanks for the replies.

I've read the link that you attached but im still confuse as to how i can find the values in the cells. Im trying to use the Dim Array now but there's still error. Can you help me check the codes cos i don't know where have i gone wrong.

Sub FindValuesOtherSheet()
    
    Dim SearchRange As Range
    Dim FoundCells As Range
    Dim FoundCell As Range
    Dim Rng As Range
    Dim MyArr As Variant
    Dim I As Long
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LookAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    
    'Fill in the search Value
    MyArr = Array("Current", "Balance")
    
    
    ''''''''''''''''''''''''''
    ' Set the variables to the
    ' appropriate values.
    ''''''''''''''''''''''''''
    
    Set SearchRange = ThisWorkbook.Worksheets("Sheet 1").Range("F7:Q13")
    
    Rcount = 0

        For I = LBound(MyArr) To UBound(MyArr)

    LookIn = xlValues
    LookAt = xlPart
    SearchOrder = xlByRows
    MatchCase = False
    
    '''''''''''''''''''
    ' Search the range.
    '''''''''''''''''''
 Set FoundCells = .Find(What:=MyArr(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                            
                            
    ''''''''''''''''''''''
    ' Display the results.
    ''''''''''''''''''''''
          If FoundCells Is Nothing Then
               Debug.Print "No cells found."
    
                Do
                    Rcount = Rcount + 1

                    FoundCells.Copy NewSh.Range("Numbers" & Rcount)
                Else
                      For Each FoundCell In FoundCells.Cells
            Debug.Print FoundCell.Address, FoundCell.Text
        Next FoundCell
    End If
    
   

    
End Sub

I've changed the code but the problem still lies on this line

After:=.Cells(.Cells.Count), _