Find Function
 

I modified some code from Aaron Blood and Ivan F Moala at:
http://www.ozgrid.com/forum/showthre...t=27240&page=1

This is a find function that I am using to find all rows that contain
"Sales" in column J, and move them from Sheet1 to Sheet2. This
function works perfectly, but I'm wanting it do do a little more. If I
can figure it out, I'd like it to delete the rows from Sheet1 that it
pastes into Sheet2. I can write a little procedure after this function
to do this, but I was wondering if it is possible to do inside the
function.

This is what I'm using right now outside of the function, and it works
fine:
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Next

Any better solutions or ideas are greatly appreciated.

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=385313

Find Function
 

Hi,
Based on the routine you referenced, try this:

Sub test()
Dim rng As Range
Set rng = Find_Range("Sales", Range("a1:a50"))
rng.Copy Worksheets("sheet2").Range("a1") 'Copy date
rng.EntireRow.Delete ' Delete original rows
End Sub


HTH

"DejaVu" wrote:


I modified some code from Aaron Blood and Ivan F Moala at:
http://www.ozgrid.com/forum/showthre...t=27240&page=1

This is a find function that I am using to find all rows that contain
"Sales" in column J, and move them from Sheet1 to Sheet2. This
function works perfectly, but I'm wanting it do do a little more. If I
can figure it out, I'd like it to delete the rows from Sheet1 that it
pastes into Sheet2. I can write a little procedure after this function
to do this, but I was wondering if it is possible to do inside the
function.

This is what I'm using right now outside of the function, and it works
fine:
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Next

Any better solutions or ideas are greatly appreciated.

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=385313

Find Function
 

This code is a whole pile more efficient than the code you are using...

Sub Test()
Call CopyCells("Sales")
End Sub

Sub CopyCells(ByVal strWordToFind As String)
Dim rngFirst As Range
Dim rngCurrent As Range
Dim rngFoundCells As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet
Dim wksToPaste As Worksheet
Dim rngToPaste As Range

Set wksToSearch = Sheets("Sheet1")
Set wksToPaste = Sheets("Sheet2")
Set rngToSearch = wksToSearch.Cells
Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)
If rngCurrent Is Nothing Then
MsgBox strWordToFind & " was not found"
Else
Set rngFirst = rngCurrent
Set rngFoundCells = rngCurrent.EntireRow
Do
Set rngFoundCells = Union(rngCurrent.EntireRow, rngFoundCells)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngFirst.Address = rngCurrent.Address
rngFoundCells.Copy rngToPaste
rngFoundCells.Delete
End If
End Sub
--
HTH...

Jim Thomlinson


"DejaVu" wrote:


I modified some code from Aaron Blood and Ivan F Moala at:
http://www.ozgrid.com/forum/showthre...t=27240&page=1

This is a find function that I am using to find all rows that contain
"Sales" in column J, and move them from Sheet1 to Sheet2. This
function works perfectly, but I'm wanting it do do a little more. If I
can figure it out, I'd like it to delete the rows from Sheet1 that it
pastes into Sheet2. I can write a little procedure after this function
to do this, but I was wondering if it is possible to do inside the
function.

This is what I'm using right now outside of the function, and it works
fine:
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Next

Any better solutions or ideas are greatly appreciated.

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=385313

Find Function
 

Is this what you are referring to: (only reference ot Moala or Blood in the
thread you posted).

Hi LTT

While in your VBE, press F1 and type in Find

Have a look @ the ensuing help files
Your help files are the best place to search for this.
Then if you require further assistance on something specific, post on that..
__________________
Kind Regards,
Ivan F Moala From the City of Sails

always a good Idea to consult the help file.

--
Regards,
Tom Ogilvy

"DejaVu" wrote in
message ...

I modified some code from Aaron Blood and Ivan F Moala at:
http://www.ozgrid.com/forum/showthre...t=27240&page=1

This is a find function that I am using to find all rows that contain
"Sales" in column J, and move them from Sheet1 to Sheet2. This
function works perfectly, but I'm wanting it do do a little more. If I
can figure it out, I'd like it to delete the rows from Sheet1 that it
pastes into Sheet2. I can write a little procedure after this function
to do this, but I was wondering if it is possible to do inside the
function.

This is what I'm using right now outside of the function, and it works
fine:
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Next

Any better solutions or ideas are greatly appreciated.

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=385313

Find Function
 

Thanks for all the replies, but I found that function to be very fas
and efficient for what I need it for. After that function is done
this is the bit of code that immediately follows it:
Worksheets("Sheet1").Range("J1").Select
Set rngSls = Range("J1", Selection.End(xlDown))
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
Do Until ActiveCell.Value < "Sales"
i = ActiveCell.Row
Cells(i, 10).EntireRow.Delete
Loop
End If
ActiveCell.Offset(1, 0).Select
Next

This code works perfectly for deleting the rows from Sheet1 after th
function has copied them to Sheet2. If I was not specific enough, I'
sorry for the confusion. My only question was: Is there a way to us
that existing function (from my link above) to make it delete the row
out of Sheet1 that it has copied to Sheet2?

Here is what I used to call the function and have it copy rows t
Sheet2:
Find_Range("Sales", Columns("J"), xlFormulas, xlWhole).EntireRow.Cop
Range("Sheet2!A2")

Everything works fine now, so I dont know whether I should change o
not, but I wanted to know if this was the "best" way to accomplish thi
task.

Thanks,

DejaV

--
DejaV
-----------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...fo&userid=2262
View this thread: http://www.excelforum.com/showthread.php?threadid=38531

Find Function
 

Jim gave you a specific code example of how you might want to do it. If you
are happy with what you have and it works, then it is a matter of your
choice.

--
Regards,
Tom Ogilvy

"DejaVu" wrote in
message ...

Thanks for all the replies, but I found that function to be very fast
and efficient for what I need it for. After that function is done,
this is the bit of code that immediately follows it:
Worksheets("Sheet1").Range("J1").Select
Set rngSls = Range("J1", Selection.End(xlDown))
For Each tmpRng In rngSls
If ActiveCell.Value = "Sales" Then
Do Until ActiveCell.Value < "Sales"
i = ActiveCell.Row
Cells(i, 10).EntireRow.Delete
Loop
End If
ActiveCell.Offset(1, 0).Select
Next

This code works perfectly for deleting the rows from Sheet1 after the
function has copied them to Sheet2. If I was not specific enough, I'm
sorry for the confusion. My only question was: Is there a way to use
that existing function (from my link above) to make it delete the rows
out of Sheet1 that it has copied to Sheet2?

Here is what I used to call the function and have it copy rows to
Sheet2:
Find_Range("Sales", Columns("J"), xlFormulas, xlWhole).EntireRow.Copy
Range("Sheet2!A2")

Everything works fine now, so I dont know whether I should change or
not, but I wanted to know if this was the "best" way to accomplish this
task.

Thanks,

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=385313

Find Function
 

Thanks Tom, and thanks Jim for you help on this.

Tom - I understand Jim gave me a specific code example, and I'm
greatful for his help. I found a solution that worked, and I also
tried Jim solution and they both worked about equally as well (as far
as I could tell). I guess what I was asking was; Whats the "*_best_*"
way to accomplish that task. I realize there are many roads a person
could take to get to the same destination, but I was just inquiring
about which way is the "*_best_*"? My apologies if I confused you.


DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=385313