RE: Endless loop?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
> > >
> > >
> > >
.

Relevant Pages

  • Re: How come biClrUsed is always zero?
    ... ' Create a clipboard CF_DIB ... Dim DIBAs Byte ... You now have an 8bpp indexed bitmap on the clipboard as a DIB ... Use p-invoke CreateStreamOnHGlobalto create a memory stream ...
    (microsoft.public.dotnet.languages.vb)
  • Clipboard Issue copying from Outlook.
    ... I have an Outlook COM Add-in written in VB6. ... It currently saves the clipboard data, ... Dim p_clipGlobal As Long ... '// Obtain a handle the the memory owned by the clipboard ...
    (microsoft.public.outlook.program_vba)
  • Re: Clipboard Issue copying from Outlook.
    ... What I usually do when I use the clipboard for that is first I use the clipboard from the Win32 API. ... Public Sub StoreEx() ... Dim p_clipGlobal As Long ... '// Obtain a handle the the memory owned by the clipboard ...
    (microsoft.public.outlook.program_vba)
  • Re: Very Slow reading excel data into an array (while opened in ne
    ... How can I read an excel file into memory without opening it? ... data array gets recycled each time it opens a new file so I don't have to ... Dim rowsMaster, colsMaster, lastCellMaster ...
    (microsoft.public.excel.programming)
  • Problems with calling avifil32.dll function in vb.net
    ... application which converts BMP Images to AVI. ... This function wants to receive a pointer to a pointer to an ... to a pointer to unmanaged memory. ... Dim oGC As GCHandle = GCHandle.Alloc ...
    (microsoft.public.vb.winapi.graphics)