View Single Post
 
Old 12-07-2014, 11:59 AM
bobby@uk bobby@uk is offline Windows 8 Office 2010 64bit
Novice
 
Join Date: May 2014
Location: uk
Posts: 2
bobby@uk is on a distinguished road
Send a message via AIM to bobby@uk
Angry Moving a row or rows on "yes" from formula

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
Reply With Quote