Error 1004 When Trying To Move Cells 24 Cols To The Right

[HowdeeDoodee]

http://www.mrexcel.com/forum/showthread.php?t=323741

From the above thread I am using the following macro...

Sub MoveOver()
Dim MoveRange As Range

Application.ScreenUpdating = False
With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))
.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter
End With
If Not MoveRange Is Nothing Then
MoveRange.Insert Shift:=xlToRight
End If
Application.ScreenUpdating = True
End Sub

I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.

Columns B through J contain data. Columns beyond J do not contain data.

Can someone give advice on getting the macro to work? Thank you in advance for replies.

 

[HowdeeDoodee]

Hi,

Its late so I'm not 100% but I think its the last one, try;

Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(, 21)

If you take all the code out of your line above, this is the parenthesis set showing we have an extra ( in the set.

(()())()

 

[Mike Blackman]

(()())()

Hi,

I removed the last one previously, I can only see 4 x ( and 4 x ) in both my last code and your last post, did you try the line of code?

 

[xenou]

I tried deleting everything K to IV saving the sheet, etc. but this had no effect on the issue.

Just to clarify, did you delete by hitting the delete key, or did you use the exact instructions Peter gave earlier:

Edit|Clear|All

The latter will clear formatting and formatting in a cell can cause this problem even if the cell is otherwise "empty".

AB

 

[xenou]

.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter

I think the effect here would be to put on the autofilter then take it right off.


Also not sure if this is a one off thing or a process to be repeated...
Loops are slow, but this might work faster than your fingers.

Sub MoveThem_CaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(Cells(x, "B").Value, "C&P") > 0 Then
        Cells(x, "B").Resize(1, 24).Copy Destination:= _
            Cells(x, "B").Offset(0, 24)
        Cells(x, "B").Resize(1, 24).ClearContents
    End If
Next x
End Sub
'--------------------------------------------------------
Sub MoveThem_NotCaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
        Cells(x, "B").Resize(1, 24).Copy Destination:= _
            Cells(x, "B").Offset(0, 24)
        Cells(x, "B").Resize(1, 24).ClearContents
    End If
Next x
End Sub

I was trying to be careful to include your precise instructions but if all you really need to do is move the value of the cell (not all the other cells in between, or empty the original cell), then just:

Sub MoveIt_CaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(Cells(x, "B").Value, "C&P") > 0 Then
        Cells(x, "B").Offset(0, 24).Value = Cells(x, "B").Value
    End If
Next x
End Sub
'--------------------------------------------------------

Sub MoveIt_NotCaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
        Cells(x, "B").Offset(0, 24).Value = Cells(x, "B").Value
    End If
Next x
End Sub

 

[HowdeeDoodee]

Just to clarify, did you delete by hitting the delete key, or did you use the exact instructions Peter gave earlier:



The latter will clear formatting and formatting in a cell can cause this problem even if the cell is otherwise "empty".

AB

Thank you. I followed the instructions exactly as recommended. I used the Edit | Clear | All scheme.

 

[HowdeeDoodee]

.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter

I think the effect here would be to put on the autofilter then take it right off.


Also not sure if this is a one off thing or a process to be repeated...
Loops are slow, but this might work faster than your fingers.

Sub MoveThem_CaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(Cells(x, "B").Value, "C&P") > 0 Then
        Cells(x, "B").Resize(1, 24).Copy Destination:= _
            Cells(x, "B").Offset(0, 24)
        Cells(x, "B").Resize(1, 24).ClearContents
    End If
Next x
End Sub
'--------------------------------------------------------
Sub MoveThem_NotCaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
        Cells(x, "B").Resize(1, 24).Copy Destination:= _
            Cells(x, "B").Offset(0, 24)
        Cells(x, "B").Resize(1, 24).ClearContents
    End If
Next x
End Sub

I was trying to be careful to include your precise instructions but if all you really need to do is move the value of the cell (not all the other cells in between, or empty the original cell), then just:

Sub MoveIt_CaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(Cells(x, "B").Value, "C&P") > 0 Then
        Cells(x, "B").Offset(0, 24).Value = Cells(x, "B").Value
    End If
Next x
End Sub
'--------------------------------------------------------

Sub MoveIt_NotCaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
        Cells(x, "B").Offset(0, 24).Value = Cells(x, "B").Value
    End If
Next x
End Sub

Thank you...

In the original thread shown at the top of this thread my needs were...

I want to cut and paste 24 cols over to the right, any group of filled cells on any row if col A in any row contains the letters C&P.

The above statement would include all cells in all rows from col A to col K.

.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter

Can you tell me, what is the above code statement designed to do?

Sub MoveIt_NotCaseSensitive()
Dim x As Long
For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
Cells(x, "B").Offset(0, 24).Value = Cells(x, "B").Value
End If
Next x
End Sub[/code]

OK, this code did work for one cell but I need all cells between A and K moved over.

Not all cells in col B are filled with the C&P indicator. Is there any way I can move all the cells in every row using a control key combination keeping in mind the macro has to first find the C&P indicator in col B, then execute the rest of the code to move the cells over?

You have all been really great about trying to solve this issue.If all else fails, because of embedded characters in the sheet, or whatever else is causing this problem...is there any way to save a sheet in tab delimited format and preserve the formatting? If there is a way to save a sheet in tab delimited format with formatting in tact, I can open that file in Word, insert needed tabs in next to C&P, and my problem is solved.

 

[xenou]

maybe:

Sub MoveThem_NotCaseSensitive()
Dim x As Long

For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    
    If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
        
        'Copy cells A-K to column X and rightward
        Cells(x, "A").Resize(1, 11).Copy Destination:= _
            Cells(x, "X")
        
        'Clear cells A-K
        Cells(x, "A").Resize(1, 11).ClearContents
    
    End If

Next x

End Sub

I'm getting a little confused. So columns A-K is 11 columns therefore I have in the above code: Cells(x, "A").Resize(1, 11), which would be A1:K1

Also, I'm not sure if when you say 24 columns to the right you mean the 24th column (Column X) or 24 columns after Column A (Column Y), or even after column B (Column Z)...But just change the column here: Cells(x, "X") as needed (the capital X can be Y, or Z).

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

With the tab stops and so on, sounds very messy. You can try Data | Text-to-Columns and use a tab delimiter just to see what happens. I wouldn't expect too much but who knows?

Let us know how this comes along. There may be more help available from other experts. Do you have some sample data to post?

AB

Edit: Offline now ... goodnight.

 

[HowdeeDoodee]

maybe:

Sub MoveThem_NotCaseSensitive()
Dim x As Long

For x = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    
    If InStr(UCase(Cells(x, "B").Value), "C&P") > 0 Then
        
        'Copy cells A-K to column X and rightward
        Cells(x, "A").Resize(1, 11).Copy Destination:= _
            Cells(x, "X")
        
        'Clear cells A-K
        Cells(x, "A").Resize(1, 11).ClearContents
    
    End If

Next x

End Sub

I'm getting a little confused. So columns A-K is 11 columns therefore I have in the above code: Cells(x, "A").Resize(1, 11), which would be A1:K1

Also, I'm not sure if when you say 24 columns to the right you mean the 24th column (Column X) or 24 columns after Column A (Column Y), or even after column B (Column Z)...But just change the column here: Cells(x, "X") as needed (the capital X can be Y, or Z).

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

With the tab stops and so on, sounds very messy. You can try Data | Text-to-Columns and use a tab delimiter just to see what happens. I wouldn't expect too much but who knows?

Let us know how this comes along. There may be more help available from other experts. Do you have some sample data to post?

AB

Edit: Offline now ... goodnight.

Alexander, your code is working fine. Everything is moved over as expected. Thank you very much.