RE: Endless loop?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 Work***
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
Active***.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 *** 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