If Cell contains specific text then Cut 3 cells to different location?

[NessPJ]

Hey all,

I have a sheet containing 2 "Areas" which each have 3 columns of Data. Both are fairly similar, yet for my use i want to do the folowing:

Area 1:
Column C, D and E
Column C = Route number
Column D = Trailer number
Column E = Departure time

Area 2:
Column G, H and I
Column G = Route number
Column H = Transporter
Column I = Departure time

I am looking for a code that will search for a specific Transporter name in Column H. When it has found it, i want it to Cut this Cell as well as the 2 Cells next to it on the same Row (so the cells in columns G and I).

Then i want the code to paste the cut cells on the first available Row of Cells in Area 1 (Columns C, D and E).

This code should loop until the Transporter name i am looking for is no longer present in Column H.

--

I tried creating a macro that will Find the transporter name and will Cut the cells in question, but i don't know how i will make my code specify to cut the other 2 cells in the same row nor do i know how to make it loop.

Any help would be greatly appreciated.

 

[NessPJ]

I tried to cook something up, but i'm no experienced coder (alas).

    Dim c As Range
    Dim SrchRng, Lastrow
    Set SrchRng = Range("G:G")
    Lastrow = Range("C65536").End(xlUp).Row
    
    Do
        Set c = SrchRng.Find("GND", LookIn:=xlValues)
        If Not c Is Nothing Then c.Cells.Cut
        Range("C & Lastrow").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Loop While Not c Is Nothing

I keep getting Debug errors on "Range("C & Lastrow").Select"
And i'm not sure if my code is working at all (lol).

 

[Peter_SSs]

I tried to cook something up, but i'm no experienced coder (alas).

    Dim c As Range
    Dim SrchRng, Lastrow
    Set SrchRng = Range("G:G")
    Lastrow = Range("C65536").End(xlUp).Row
    
    Do
        Set c = SrchRng.Find("GND", LookIn:=xlValues)
        If Not c Is Nothing Then c.Cells.Cut
        Range("C & Lastrow").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Loop While Not c Is Nothing

I keep getting Debug errors on "Range("C & Lastrow").Select"
And i'm not sure if my code is working at all (lol).

The syntax for that line should be

Range("C" & Lastrow).Select

but I'm not sure it will quite do what you want anyway.

However, ..

1. It is rare that you need to select anything in vba and selecting slows your code.

2. Your code from post #2 does not match your description from post #1. In #1 you said you wanted to search column H and then cut cells from columns G:I and move them. Your code is searching column G and only cutting that value. If you can clarify just what is required, some code can be suggested.

 

[NessPJ]

Hey,

Thanks for clearing out the syntax.

I don't know how to make my code select the Cells next to the ones in column G yet (neither when cutting nor pasting), hence it was not in the code.

 

[Peter_SSs]

You didn't directly address my question about the change in column to search. Is it now that you want to search in column G and cut G:I and paste into C:E (next available row)?

 

[NessPJ]

You didn't directly address my question about the change in column to search. Is it now that you want to search in column G and cut G:I and paste into C:E (next available row)?

Hi,

Sorry if i wasn't clear enough. You are exactly right i want to search in Column H, then Cut from G:I and paste into the first available Row on C:E.

 

[Peter_SSs]

This is how I would appraoch it. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SearchAndMove()<br>    <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> SrchCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "G"<br>    <SPAN style="color:#00007F">Const</SPAN> SrchTxt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "GND"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns(SrchCol)<br>        <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=SrchTxt, After:=.Cells(1, 1), _<br>            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, _<br>            MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Do</SPAN><br>                Found.Resize(1, 3).Cut _<br>                    Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)<br>                <SPAN style="color:#00007F">Set</SPAN> Found = .FindNext<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[NessPJ]

Hey,

Thanks a lot for the help so far!

I put your code in a copy of my excelfile, it seems to run without any errors yet nothing seems to change.
I put a copy of the file up, if you would be interested in looking at it perhaps. Click.

PS.
Don't worry about the missing "Application.Screenupdating". I usually put those at the start and end of my macro once i am done creating/testing it (So i can see where it gets stuck, if anything goes wrong).

 

[Peter_SSs]

I'm sorry but many helpers here, including me, choose not to receive files from other members or download from other sites. There are a number of reasons including ..

- Having done so in the past it often turns out that the workbook/worksheet is quite complex and takes a lot of time to become familiar with. (If so, it may mean the problem is really too complex for a free public forum like this.)

- It tends to take the onus off the 'asker' to distil their problem to something clear and concise and becomes more "Here's my workbook, can you figure out what I'm trying to do and fix it?"

- It defeats the purpose of the forum being a public forum as, due to security issues at work sites, many users are unable to download such files.

You will get many more potential helpers if you explain your problem clearly in words and, if needed, post a small screen shot or two directly in your post. My signature block below suggests 3 ways you can do that. Test them out in the Test Here forum.

In the mean time ..

- Are you sure that the SrchTxt occurs in column G?

- Place you cursor anywhere in the code and step through a line at a time with F8 and see what happens. This may shed some light on what the problem is.

- What Excel version are you using?

 

[NessPJ]

Alright, i understand.

Haha my bad...the text to search for it in Column "H".
I will try and see if its working correctly now.

Thanks for the tip on stepping through the code using F8 (I didn't know about that yet).

I'm using Excel 2003.

[Edit]

It seems now, when the value i am searching for is found it will copy the cell in column H, I and J.
(Rather then G, H and I).

I think its in the code here:

Found.Resize(1, 3).Cut _

Would you know how i go from 3 columns to the right (from the starting position) to one backwards and one forward?