Results 1 to 14 of 14

Compile Error. Sub of Function not Defined.

Threaded View

  1. #1
    Registered User
    Join Date
    07-14-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    32

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1