Cutting and Pasting info based on specific values

clara200

New Member
Joined
Jul 19, 2006
Messages
28
Hey Everyone,

I am having a little trouble with one of my macros in excel. I am trying to apply the code I was using for a different worksheet to another worksheet. The problem I have is in this worksheet the information I am filtering for is different. It filters for the info for CRRET and 03/J transactions fine. The problem is there is also info in coloumn J that has only one unique property about it. Some of the transactions begin with the letter D ans then a number eg D123456. The other transactions are like CRRET1234 and 03/J123 so it filters them fine, but it won't filter any of the transactions that begins with D.

Is there a way of filtering for say "Dnumber" i would say that would do the trick.

Code:
Sub Macro9()
    Application.ScreenUpdating = False
    Sheets("RET").Select
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CRRET Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of D Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of 03/J1 Transactions"
   
    For MY_ROWS = Range("J65536").End(xlUp).Row To 1 Step -1
    Select Case Left(Range("J" & MY_ROWS).Value, 5)
        Case "CRRET"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "D"
            Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "03/J1"
            Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).Offset(1, 0)
             Rows(MY_ROWS).Delete
        End Select
    Next MY_ROWS
End Sub

Thanking you in advance,
Seán.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

clara200

New Member
Joined
Jul 19, 2006
Messages
28
thanks Andrew for your reply. that worked like a dream. it was so simple and I couldn't even think of it.

any chance you know or anyone for that matter how you can manipulate the code to include more that just three headings, and extracting just the criteria of three items. Eg CRRET, D0000, 03/J4, CQIND etc.

The code was given to me by "onlyadrafter" and I have been trying to include a fourth item but have been failing miserably
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What code have you tried to incluse a fourth item? You just need to add another Case statement.
 

clara200

New Member
Joined
Jul 19, 2006
Messages
28

ADVERTISEMENT

Code:
Sub ABC()
Application.ScreenUpdating = False
    Sheets("RET").Select
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CQ Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of D000000 To D999999 Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CRRET Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of 03/J0 To 03/J4 Transactions"
   
    For MY_ROWS = Range("J65536").End(xlUp).Row To 1 Step -1
    Select Case Left(Range("J" & MY_ROWS).Value, 5)
        Case "CQ"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "D000000" To "D999999"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "CRRET"
            Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "03/J0" To "03/J4"
            Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).Offset(1, 0)
             Rows(MY_ROWS).Delete
        End Select
    Next MY_ROWS
End Sub

When I enter this it my sheet goes it sorts everthing incorrectly and I have discovered that not only does it sort the transactions incorrectly it deletes some as well

Seán
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What's?

Case "CQ"
Case "D000000" To "D999999"

They are 2 and 7 characters respectively, not the 5 extracted by:

Select Case Left(Range("J" & MY_ROWS).Value, 5)
 

clara200

New Member
Joined
Jul 19, 2006
Messages
28

ADVERTISEMENT

Code:
Sub ABC()
Application.ScreenUpdating = False
    Sheets("RET").Select
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CQRET000 To CQRET999 Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of D000000 To D999999 Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CRRET Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of 03/J0 To 03/J4 Transactions"
   
    For MY_ROWS = Range("J65536").End(xlUp).Row To 1 Step -1
    Select Case Left(Range("J" & MY_ROWS).Value, 8)
        Case "CQRET000" To "CQRET999"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "D000000" To "D999999"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "CRRET0000" To "CRRET9999"
            Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "03/J0" To "03/J4"
            Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).Offset(1, 0)
             Rows(MY_ROWS).Delete
        End Select
    Next MY_ROWS
End Sub

Right the code is working fine now except for one small little thing intstead of the CQRET transactions coming in under their own heading. They are comin in under the D00000 heading. apart from that everyting to my eyes looks ok
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That's because you have the same number of End(xlUp) statements for both cases. CQRET should be 4 not 3.
 

clara200

New Member
Joined
Jul 19, 2006
Messages
28
when you say add another End(xlUp) Statement where do you mean.

For CQRET there is already four and only three for D000

Case "CQRET000" To "CQRET999"
Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
Rows(MY_ROWS).Cut Destination:= _
Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
Rows(MY_ROWS).Delete


Case "D000000" To "D999999"
Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
Rows(MY_ROWS).Cut Destination:= _
Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
Rows(MY_ROWS).Delete
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, I'm sure there were only 3 when I read it.

When only the headings are present End(xlUp) 4 times will take you to the first header. But when data is added below the header(s) it won't, unless the source data is sorted in reverse order to the order of the headings.
 

Forum statistics

Threads
1,136,623
Messages
5,676,857
Members
419,656
Latest member
lironprofit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top