Excel Macros Cut and Paste Keyword

Closed
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am new to excel macros, but would like to use it. I have a list of 100,000 addresses. I need to find addresses with the name "BOX", then select that cell and the next three cells on its right, and cut and paste them 4 cells to the left. It seems really simple but I keep getting errors. Is it possible to execute this command?

3 replies

Hi Whitebears,

Let me start by telling you that I am not a VB expert.

Here is my attempt to solve you query:

Sub test()
Set MyPlage = Range("E1:E10000")
    For Each Cell In MyPlage
    If Cell.Value = "BOX" Then
            Range(Cell.Offset(0, 0), Cell.Offset(0, 3)).Copy
            Cell.Offset(0, -4).PasteSpecial
            Range(Cell.Offset(0, 0), Cell.Offset(0, 3)).ClearContents
        End If
    Next
End Sub


Take a look a this line: Set MyPlage = Range("E1:E10000")
Please adjust the letter E to the letter corresponding to the column you want to find the word "BOX" in.
Then adjust the range you want to look in, maybe it's E5:E15000.
Now give it a try.

How to implement the code:
When in excel press Alt+F11.
VB will open.
Now goto the top menu's Insert > Module.
Now copy/paste the code in the white window.
Now go back to excel and press Alt+F8 and execute the macro test.

Best regrads,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
769
What kind of error ? Could it be that you have merged cells
Hi Trowa,

I tried it and it did not do anything. It's probably missing one little thing. Thanks for your work so far.

I also tried this and it didn't work either.

Sub test()
Set MyPlage = Range("J15000:J97986")
For Each Cell In MyPlage
If Cell.Value = "BOX" Then
Range(Cell.Offset(0, 0), Cell.Offset(0, 3)).Cut
Cell.Offset(0, -4).Paste
End If
Next
End Sub
Hi Whitebears,

After reading your adjustments I noticed you used Cut instead of Copy and Paste instead of PasteSpecial, but both doesn't work in VB (at least as far as I know). That is why I used Copy in combination with ClearContents to simulate Cut.

One thing I was doubtfull about is this line in your first post:
quote
I need to find addresses with the name "BOX"
unquote

Do you mean "BOX" is the only value of the cell or do you mean that it is part of the value of the cell?

If it is the latter option then hopefully Rizvisa could jump in since I spend almonst an hour figuring out how to get that done.

Maybe you could post your workbook using a site like www.speedyshare.com and re-explain want you want to get done.

Best regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
769
Trowa, cut does work in macro too. It is paste that is cause of issue here. This is my rule of thumb (dont know if it is true or not, but has rarely failed me). I use "paste" only with ActiveSheet (ActiveSheet.Paste) and pastespecial with cells and ranges (unless it is offset like here [ Range("B2").PasteSpecial ])

Whitebears, try this.

Sub test()   
    Set MyPlage = Range(Range("J15000:J16000").Address)   
       
    For Each Cell In MyPlage   
        If Cell.Value = "BOX" Then   
            Range(Cell.Offset(0, 0), Cell.Offset(0, 3)).Cut   
            Cell.Offset(0, -4).Select   
            ActiveSheet.Paste   
        End If   
    Next   
End Sub
Thanks for the clearification Rizvisa. VB does work in mysterious ways.

Can you explain why you would use .Address in the first line of code since both (with and without .Address) will work fine.

And what do I need to do if I want the code to work on cells with more than just "BOX" in the cell like "AAA BOX" or "BOXAAA". I thought I simply had to add an "*" like in
If Cell.Value =  "*BOX"
or
If Cell.Value = "*" & "BOX"
But both don't work. What would you do?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
769
Because I am stupid :P. There was no need of ".address" I dont know what I was thinking. Sorry for confusion.

For wild card, you can use use INSTR

    If (InStr(1, Cell.Value, "BOX") > 0) Then 
    ' *BOX* 
     
        If (Cell.Value = "BOX") Then 
            'BOX 

        ElseIf(Left(Cell.Value, Len("BOX")) = "BOX") Then 
            ' only reason I am using LEN is that if you change BOX to some thing else, you dont have find the len. I am lazy 
            ' BOX* 

       ElseIf(Right(Cell.Value, Len("BOX")) = "BOX") Then            
            ' *BOX 

        Else 
            ' *BOX *             
        End If 
         
    End If
You are pretty smart for a stupid guy for writing all those macro's and you are pretty active for a lazy guy helping out all those people on this forum! haha

Anyway thanks for providing another piece of the puzzle called VB.