RE: Endless loop?

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 *** equals "true" then cut that row and paste it into a new ***.
> > >
> > > 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 Work***
> > > 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
> > > Active***.Paste
> > > Set rngFound = rngToSearch.FindNext
> > > Loop Until rngFound Is Nothing
> > > End If
> > > Application.ScreenUpdating = True
> > > Application.Calculation = xlCalculationAutomatic
> > > End Sub
> > >
> > >
> > >
.

Quantcast