Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-07-2014, 11:59 AM
bobby@uk bobby@uk is offline Moving a row or rows on "yes" from formula Windows 8 Moving a row or rows on "yes" from formula Office 2010 64bit
Novice
Moving a row or rows on "yes" from formula
 
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
  #2  
Old 12-07-2014, 04:56 PM
whatsup whatsup is offline Moving a row or rows on &quot;yes&quot; from formula Windows 7 64bit Moving a row or rows on &quot;yes&quot; from formula Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Hi Bobby

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

Cheers
Reply With Quote
  #3  
Old 12-07-2014, 06:41 PM
bobby@uk bobby@uk is offline Moving a row or rows on &quot;yes&quot; from formula Windows 8 Moving a row or rows on &quot;yes&quot; from formula Office 2010 64bit
Novice
Moving a row or rows on &quot;yes&quot; from formula
 
Join Date: May 2014
Location: uk
Posts: 2
bobby@uk is on a distinguished road
Send a message via AIM to bobby@uk
Default

Brilliant, works a treat.

Thank you very much.

Bob
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving a row or rows on &quot;yes&quot; from formula Use formula in "Age" field to calculate age rossjp Word 9 12-30-2013 12:31 AM
using merge fields in "if then else" formula mike0919 Mail Merge 4 03-27-2013 02:50 PM
Moving a row or rows on &quot;yes&quot; from formula Word Macro to find and delete rows that contain adjacent cells containing "." AlexanderJohnWilley Word VBA 7 11-08-2012 10:15 AM
Moving a row or rows on &quot;yes&quot; from formula How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM
Moving a row or rows on &quot;yes&quot; from formula Excel 07 formating 17 cells "Need Formula" Raner Excel 2 05-30-2010 02:07 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft