Excel Macros Cut and Paste Keyword

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

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?

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.