Excel Macros Cut and Paste Keyword
Closed
whitebears
-
Aug 19, 2010 at 01:00 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 24, 2010 at 10:23 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 24, 2010 at 10:23 AM
Related:
- Excel Macros Cut and Paste Keyword
- Macros in excel download - Download - Spreadsheets
- Excel free download - Download - Spreadsheets
- Excel marksheet - Guide
- Number to words in excel - Guide
- Cut pdf download - Download - PDF
3 responses
Hi Whitebears,
Let me start by telling you that I am not a VB expert.
Here is my attempt to solve you query:
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 20, 2010 at 09:10 AM
Aug 20, 2010 at 09:10 AM
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
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 23, 2010 at 09:39 AM
Aug 23, 2010 at 09:39 AM
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.
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
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?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 24, 2010 at 09:50 AM
Aug 24, 2010 at 09:50 AM
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
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