Hi. I am trying to make a spread sheet copy a row from a yes or no formula.
i have this formula in a cell "L"
=IF(H5<=$B$2,"yes","no")
which gives me a yes or no fine.
i then have a button with a macro behind it that i require to copy the rows with yes in them and paste the into another sheet
here is my code
Sub Move()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String



Set rngPaste = Sheets("Due").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("L")
Set rngFound = rngToSearch.Find(what:="Yes", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

rngFoundAll.EntireRow.Copy Destination:=rngPaste
' rngFoundAll.EntireRow.Delete 'Optional to Delete
MsgBox "All completed items to moved to the done sheet."
End If
End Sub
my problem is this does'nt detect the yes and just skips to the "there are no items to move.
I think its because its a formula in L not a physical yes ??. but how do i get round the issue?
any help gratefully recieved

Hi Bobby

Don't lookin xlformulas but "lookin:=xlvalues" since you are after the result of a formula

Cheers

Brilliant, works a treat.

Thank you very much.

Bob