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
Bookmarks