PDA

View Full Version : [SOLVED] Cut and paste



austenr
09-29-2005, 08:18 AM
I am trying to use this code to search for several variables. If it is found I need to cut that cell and the two to the immediate right and paste them two cells to the right. I am getting an object cannot support that method error.


Sub CandP()
FinalRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To FinalRow Step 2
If Cells(i, 6).Value = 3501 Or 3511 Or 3521 Or 7521 Or 7525 Or 7541 Then
Cells(i, 6).Resize(1, 3).Cut Destination:=Cells(i, 8).Paste
End If
Next i
End Sub

TonyJollans
09-29-2005, 08:35 AM
You can't Cut to a destination. Try Copying to the destination and then deleting the original cells.

austenr
09-29-2005, 08:38 AM
Could you post an example

TonyJollans
09-29-2005, 09:04 AM
Sorry! :omg2: Can I start again?

Your IF statement is wrong. Comparands cannot be implied, so you must say

If Cells(i, 6).Value = 3501 _
Or Cells(i, 6).Value = 3511 _
Or Cells(i, 6).Value = 3521 _
Or Cells(i, 6).Value = 7521 _
Or Cells(i, 6).Value = 7525 _
Or Cells(i, 6).Value = 7541 Then

Also the .Paste at the end of the Desination is causing your problem. Remove it ..

Cells(i, 6).Resize(1, 3).Cut Destination:=Cells(i, 8)

austenr
09-29-2005, 09:33 AM
everything works with those changes. Now all I need to do is clear the contents of (i, 6) which I did by

cells(i, 6).value = ""

which works the first time through but does not on the second time forward in the loop. Any ideas?

TonyJollans
09-29-2005, 10:34 AM
I was wrong about Cut not working with a Destination, so you shouldn't need to clear anything. Did you change it to Copy or does Cutting not do as you want?

austenr
09-29-2005, 10:39 AM
Actualy no. I am posting a sample workbook. All I need to do is cut what is in Col F and 7 cells to the right and paste it in Col G. In otherwords everything over 1 column. Also I am getting a Next with no for statement error although that is obviously not the case. Thanks

TonyJollans
09-29-2005, 11:32 AM
Sorry my mis-information has confused. Change Copy back to Cut - that'll sort out the deletion.

The slightly confusing "Next without For" is because you're within an If and VBA has found a Next without a corresponding For inside the If. Either add and End If after the Copy, or put a continuation underscore after the Then.

So the whole code looks like this

Sub CandP()
FinalRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To FinalRow Step 2
If Cells(i, 6).Value = 3501 Or _
Cells(i, 6).Value = 3511 Or _
Cells(i, 6).Value = 3521 Or _
Cells(i, 6).Value = 7521 Or _
Cells(i, 6).Value = 7525 Or _
Cells(i, 6).Value = 7541 Then _
Cells(i, 6).Resize(6, 8).Cut Destination:=Cells(i, 7)
Next i
End Sub

austenr
09-29-2005, 11:47 AM
hhmmm...Take a look at the sample workbook I have attached after a few modifications. When you run the macro it copies and pastes the first three rows but not the last 2. I could not get the .cut function to work so I used copy to a range which is what I wanted. I can run another routine to clear col f. No big deal.