Cut/paste range within sheet & Runtime error 1004

flip

New Member
Joined
Jul 6, 2005
Messages
4
Hi all,

I have a sheet that produces a range of results (ID numbers) starting at column P and extending for anything up to 11 columns to the right, i.e. could end at column Z but could also be just 2 columns of data, ending at column Q. I want to "align" all this data so that the LAST column of data is always AA, i.e. have the blanks on the left not the right.

so conceptually from this:
(1) (4) (19) (6) (empty) (empty) (empty)

I want to rearrange like:
(empty) (empty) (empty) (1) (4) (19) (6)

(all starting from col.P as the leftmost column with data and ending up with everything shifted across to end at col.AA)

I've already put a formula into col.AB that returns the variable "SIDOffset" as the number of blanks in range P:AA, i.e. if there are 8 ID numbers (P, Q, R, S populated, all others blank) then this will show 4.

I have tried the following code but it doesn't work: Run-time error '1004', Application-defined or object-defined error. The problematic line is highlighted below

With Sheets(TargetSheet)
* other code*
For Each cell3 in .Range("P2:P6")
SIDOffset=.Range("AB" & cell3.Row).Value
.Range(Cells(cell3.Row, 16), Cells(cell3.Row, 16 + (11 - SIDOffset))).Cut
.Range(Cells(cell3.Row, 16 + SIDOffset), Cells(cell3.Row, 27)).Paste


Tried the same thing referencing Sheets(TargetSheet).Range(cells(cell3.Row etc. but it still didn't work.

So I even tried this, just copying data across before emptying the cells to the left, but it also doesn't work:

With Sheets(TargetSheet)
* other code*
For Each cell3 in .Range("P2:P6")
SIDOffset=.Range("AB" & cell3.Row).Value
.Range(Cells(cell3.Row, 16 + SIDOffset), Cells(cell3.row, 27)).Value = .Range(Cells(cell3.Row, 16), Cells(cell3.Row, 16 + (11 - SIDOffset))).Value


Thanks very much to anyone who can help, appreciate your time!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Not tested, but try putting a period before the "Cells(..." references, too, since you're referencing it inside the With Sheets(... / End With.
 
Upvote 0
Hi to all.
This is what I came up with, hope it helps:
VBA Code:
    Application.ScreenUpdating = False            '<- added
    With Sheets(TargetSheet)
        For Each cell3 In .Range("P2:P6")
            SIDOffset = .Range("AB" & cell3.Row).End(xlToLeft).Column '<- changed
            If SIDOffset < 16 Then GoTo skipempty '<- skip if row is empty (maybe not necessary, added just to be sure)
            .Range(Cells(cell3.Row, 16), Cells(cell3.Row, SIDOffset)).Cut
            .Cells(cell3.Row, 27 - (SIDOffset - 16)).Select '<- changed
            Sheets(TargetSheet).Paste             '<- added
skipempty:                                        '<- added
        Next cell3
    End With
    Application.ScreenUpdating = True             '<- added
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top