macro Cell designation
 

When I run this macro:

-------------------------------------------------------

For z = 5 To 100
Range("Jz").Select
Application.CutCopyMode = False
Selection.Cut
Range("J(z-1)").Select
ActiveSheet.Paste

Next

----------------------------------------------------------------

I get message:

-----------------------------------------------------------------

Run time error '1004':

Method ' Range' of object _'Global failed"

----------------------------------------------------------------

Please tell me how to correct the code.

Thanks.

Ed

macro Cell designation
 

use Cells instead of Range i.e.

For z = 5 To 100
Cells(z,10).Select
Application.CutCopyMode = False
Selection.Cut
Cells(z-1,10).Select
ActiveSheet.Paste

Next




"Ed" wrote:


When I run this macro:

-------------------------------------------------------

For z = 5 To 100
Range("Jz").Select
Application.CutCopyMode = False
Selection.Cut
Range("J(z-1)").Select
ActiveSheet.Paste

Next

----------------------------------------------------------------

I get message:

-----------------------------------------------------------------

Run time error '1004':

Method ' Range' of object _'Global failed"

----------------------------------------------------------------

Please tell me how to correct the code.

Thanks.

Ed

macro Cell designation
 

Why not cut the whole range and move up one row instead of looping

To fix your code (you should do this without selecting):

For z = 5 To 100

Range("J" & z).Select

Application.CutCopyMode = False

Selection.Cut

Range("J" & z-1).Select

ActiveSheet.Paste

Next


--
Damon Longworth

2006 West Coast Excel / Access User Conference
October 25-27th, 2006
Marina del Rey Hotel
Marina del Rey, California USA
http://www.exceluserconference.com/WCEUC.html

"Ed" wrote in message
...

When I run this macro:

-------------------------------------------------------

For z = 5 To 100
Range("Jz").Select
Application.CutCopyMode = False
Selection.Cut
Range("J(z-1)").Select
ActiveSheet.Paste

Next

----------------------------------------------------------------

I get message:

-----------------------------------------------------------------

Run time error '1004':

Method ' Range' of object _'Global failed"

----------------------------------------------------------------

Please tell me how to correct the code.

Thanks.

Ed

macro Cell designation
 

Ed,
As Damon pointed out, Range("Jz") does not evaluate to the cells J5, J6,
J7...., but if anything to the column JZ, which is out of range for versions
of excel before 2007.
So what you need is Range("J" & z).

Also, .Select is seldom necessary or desirable.
And you can the whole thing in one operation :

With Range("J5:J100")
.Cut Destination:=.Offset(-1,0)
End With

NickHK

"Ed" wrote in message
...

When I run this macro:

-------------------------------------------------------

For z = 5 To 100
Range("Jz").Select
Application.CutCopyMode = False
Selection.Cut
Range("J(z-1)").Select
ActiveSheet.Paste

Next

----------------------------------------------------------------

I get message:

-----------------------------------------------------------------

Run time error '1004':

Method ' Range' of object _'Global failed"

----------------------------------------------------------------

Please tell me how to correct the code.

Thanks.

Ed

macro Cell designation
 

Thank you both, Damon and Nick.

Problemis solved.

Thanks again,

ed English