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

John,

If you look in help for FIND, you will see that it will go on for ever if
you don't stop it.
What you need to do is to capture the first address found and check that
Find has not looped back to that address, at the end of your Loop.

Set rngFound = rngToSearch.Find("true")
FirstAdd =rngFound
...
..
..
..
..
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing Or FirstAdd = rngFound

Henry

"John" wrote in message
...
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

Nevermind, I think It is due to the 7000 rows I am trying to sort through...

"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

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

On a smaller sample of data, I have found that the code actually cuts and
copies every row... not just rows that contain "true" in the 24th column.
That is particularly perplexing....

The cut and paste part of the code seems to be working fine however.


"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

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

is there a way to tell the code to "find" only cell values... rather than the
formula itslef? In other words, is there a way that i won't have to copy and
paste values for the code to work.

"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

John,

Are you looking for something like

set rng1 = Sheets("Sheet1").Range("A1:D5")
set rng2 = Sheets("Sheet2").Range("M6:P10")
rng2.value = rng1.value

or
Sheets("Sheet2).Cell(x,y).value = Sheets("Sheet1").Cell(a,b).value

no copy/paste needed.
--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
is there a way to tell the code to "find" only cell values... rather than
the
formula itslef? In other words, is there a way that i won't have to copy
and
paste values for the code to work.

"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

steve,

thanks for the suggestion. I am not sure what that code is doing. Could
you explain what that does so I can figure out how to stick it into my code?

"STEVE BELL" wrote:

John,

Are you looking for something like

set rng1 = Sheets("Sheet1").Range("A1:D5")
set rng2 = Sheets("Sheet2").Range("M6:P10")
rng2.value = rng1.value

or
Sheets("Sheet2).Cell(x,y).value = Sheets("Sheet1").Cell(a,b).value

no copy/paste needed.
--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
is there a way to tell the code to "find" only cell values... rather than
the
formula itslef? In other words, is there a way that i won't have to copy
and
paste values for the code to work.

"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

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

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

apologies if what I am refering to in the copy and paste... here is the
slightly modified code... I am copying and pasting column 24 to get values
into that column...

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Range("x6").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")
firstadd = rngFound
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

"John" wrote:

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

john,
I have to go do my end of day stuff. Steve has the solution i think. if i
can't get back today, post again tomorrow and i will get back with you on
this thread.

Sorry i have to leave.
regards
FSt2
"John" wrote:

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

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