Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 334
Default Find "Not Equal"

I have this code (thank you very much, really) but I need to modify it a bit.
I only want to copy and paste the first four columns AND I only want the
lines that DO NOT have the word "Hold" in column a:

Sub MoveNotHold()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String

Set rngPaste = Sheets("Roll").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = Sheets("Alpha").Range("A1:E99")
Set rngFound = rngToSearch.Find(What:="Hold", _
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
End If
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Find" a wildcard as a place marker and "replace" with original va Eric Excel Discussion (Misc queries) 1 January 27th 09 06:00 PM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
"FIND" generates "Type mismatch" error quartz[_2_] Excel Programming 5 November 16th 04 03:29 PM


All times are GMT +1. The time now is 04:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"