Re: Endless loop?

John,

Instead of selecting a range, copying it, selecting another range, and
pasting.
You first set the range to copy from
set rng1 = Sheets("Sheet1").Range(Cells(rw1, col1), Cells(rw2,
col2))

rw1, rw2, col1, and col2 can be variables that you first determine

Set rng2 = Sheets("Sheet2").Range(Cells(rw1+x,col1+y),Cells(rw2 + x,
col2+y)
This must be a range of the same size.
x & y are offset values if you don't want to put them into the equivalent
range.

Sheets are included only if you want to use 2 separate worksheets.

than you just exchange the values from one range to the other:
rng2.Value = rng1.Value
works on a multi-cell range on on a single cell.

Range("x6").Select
> Range(Selection, Selection.End(xlDown)).Select

you can replace this with
dim rw1 as long, rw2 as long, col1 as long, col2 as long, x as long, y as
long

rw1 = 6
rw2 = Range("X6").End(xlDown).Row
col1 = 24

set rng1 = Range(cells(rw1,col1),cells(rw2,col1))

now you just need to do the same for rng2

let me know if this helps.

steveB

Remove "AYN" from email to respond
"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:54BCCA13-CAF3-4FD9-8956-C9632141A6ED@xxxxxxxxxxxxxxxx
> 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
>> > > > >
>> > > > >
>> > > > >


.