John,

Keep on Exceling....

--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
very cool, nice job again.

"STEVE BELL" wrote:

John,

Glad to have helped! Thanks for the feed back.

One thing I do is to experiment in Excel with the Macro Recorder on.
That's how I found the Find code for you...

--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
Steve, thanks for all the help. I really like the qualifications on
the
find
function.

"STEVE BELL" wrote:

John,

I forgot to add .row
Without .Row Excel doesn't know what you want...

rw2 = Sheets("NI").Range("A9").End(xlDown).Row + 1

and you were correct to delete rw1 = 0...
--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
Steve, I get a type mismatch on this row...

rw2 = Sheets("NI").Range("A9").End(xlDown) + 1

I also took out the line; rw1 = 0 since I was getting the message
box
for
No
NI Trades all the time... not sure if that is causing a problem.


"STEVE BELL" wrote:

John,

If I followed your code - you just want to find each occurance of
True
and
transfer it to sheet NI (?)
This code should do it without selecting anything.

let me know if it works.
================================================== =
Sub NI()
'
Dim rw1 As Long, rw2 As Long
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(24)

' replace formula with value
rw1 = wks.Range("E4").End(xlDown).Row
wks.Cells(rw1, 5).Value = wks.Cells(rw1, 5).Value

' Find all occurances of True and transfer to sheet NI
Do Until rw1 = 0
On Error Resume Next
rw1 = 0
rw1 = rngToSearch.Find(What:="true", After:=ActiveCell,
LookIn:=xlValues,
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Row
On Error GoTo 0
If rw1 0 Then
rw2 = Sheets("NI").Range("A9").End(xlDown) + 1
Sheets("NI").Cells(rw2, 1).Value = wks.Cells(rw1, 24).Value
wks.Cells(rw1, 24).ClearContents
Else
rw1 = 0
MsgBox "No NI Trades Found"
End If
Loop

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
================================================== =
Set rngFound = rngToSearch.Find("true")
If rngFound Is Nothing Then
MsgBox "No NI Trades Found"
Else
Do
rngFound.EntireRow.Cut
Sheets("NI").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If


--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
steve, looks great but I am getting several errors. would you
mind
putting
in my code that i posted earlier? Or just explain where this new
code
should
go... Looks like it should work though.

Thanks!

"STEVE BELL" wrote:

Found this little trick when using find:

Selection.Find(What:="true", After:=ActiveCell,
LookIn:=xlValues,
LookAt
_
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
MatchCase:= _
False).Activate

The trick comes by using: LookIn:=xlValues
Only picked cells that returned TRUE... Ignored cells that
returned
FALSE.
--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
the problem now is that excel searches column 24 and finds
every
column
since
I have "true" in the formulas... As I said... if I copy and
paste
values
over the entire column (24) then the code works.

My question is... "Is there anyway to tell excel to search for
cell
values
that equal true and not just "true" in the formula that is in
each
cell.
For
example, =IF(E5=t0983101!$X$4,TRUE,FALSE)... this triggers the
cut
and
paste
in the macro... even though the formula result of this cell is
false.

Thanks again.

"FSt1" wrote:

hi
yes it seems to work fine but what is happening this the
copy/paste
uses
the
clipboard and it eats up the memory. crash usually occurs
with
out
of
memory
messages. this in not unique to xl. i had the same probem
with
lotus.
cut
seems to be worse that copy.
have you tried henry's suggestion?

FSt1

"John" wrote:

If I copy and paste values in my formula (true/false)
column
it
all
works
fine... again assuming a smaller sample size...

"FSt1" wrote:

hi,
i think it is the cut/copy part of your do loop that is
crashing
the
macro.
it has been my experience that the cut and copy commands
should
not
be used
in a macro excessively. once or twice is ok but with
inside
a
loop, i
wouldn't have done it that way.
you can add this just before the cut command.

Application.CutCopyMode = False

that will clear the clipboard.
if that don't work then you will have to use another way.
maybe
with
variable. post back if it don't work and is before 4:00Pm
EDT
US.

regards

FSt1


"John" wrote:

I have a piece of code that I am experimenting with and
it
crashes
my
excel... have I set up an endless loop? The idea is
that
if
a
certian column
in the sheet equals "true" then cut that row and paste
it
into a
new sheet.

Thanks for the help!

Sub NI()
'

'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(24)

Set rngFound = rngToSearch.Find("true")
If rngFound Is Nothing Then
MsgBox "No NI Trades Found"
Else
Do
rngFound.EntireRow.Cut
Sheets("NI").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub