Results 1 to 6 of 6

Assist in modifying code to locate an exact phrase in a cell

Threaded View

Jsin Assist in modifying code to... 06-23-2010, 08:57 AM
Jsin Re: Need help modifying code 06-23-2010, 11:05 AM
shg Re: Assist in modifying code... 06-23-2010, 11:42 AM
Jsin Re: Assist in modifying code... 07-09-2010, 02:02 PM
shg Re: Assist in modifying code... 07-09-2010, 02:41 PM
Jsin Re: Assist in modifying code... 07-09-2010, 02:52 PM
  1. #1
    Registered User
    Join Date
    09-23-2008
    Location
    Ohio, USA
    Posts
    6

    Post Assist in modifying code to locate an exact phrase in a cell

    Hello, I need help with the below code to find exactly what's listed. Right now if it finds that in any cell in the row it deletes the row (which worked wonderfully for what I needed it to do before). I need to make it find the exact phrase. Usually it's located in column A, once in a while it may be in column B.

    If a sample sheet is needed please advise. It'll take me some time to 'clean up' the data for public posting.

    Edit! - What happens is we have locations such as "A-A-0" and "NA-A-0". I need this code to find just the A-A-0 one and not delete the NA location too.

    Dim rng As Range
     Dim what(50) As String
    
    Application.ScreenUpdating = False
    
        
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(22, 1), Array(30, 1), Array(35, 1), _
            Array(66, 1), Array(73, 1), Array(77, 1), Array(87, 1), Array(92, 1), Array(101, 1), Array( _
            109, 1)), TrailingMinusNumbers:=True
        Columns("A:A").EntireColumn.AutoFit
        Range("C:C,D:D,E:E,F:F,H:H,I:I,L:L").Select
        Range("L1").Activate
        Selection.Delete Shift:=xlToLeft
        Range("1:7,65:73,131:139,197:205,263:271,329:337,395:403,461:469,527:535,593:601,659:667,725:733,791:799,857:865,923:931,989:997").Select
        Selection.Delete Shift:=xlUp
        Columns("A:A").Select
        Selection.Cut
        Columns("C:C").Select
        Selection.Insert Shift:=xlToRight
        Range("A1").Select
        Range("A1:E2000").Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range _
            ("B1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
            :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
            DataOption2:=xlSortNormal
        ActiveCell.SpecialCells(xlLastCell).Select
        Range("A1").Select
        
    what(1) = "A-A-0"
    what(2) = "A-A-1"
    what(3) = "A-A-2"
    what(4) = "A-B-0"
    what(5) = "A-B-1"
    what(6) = "A-B-2"
    what(7) = "A-C-0"
    what(8) = "A-C-1"
    what(9) = "A-C-2"
    what(10) = "A-D-0"
    what(11) = "A-D-1"
    what(12) = "A-D-2"
    what(13) = "A-E-0"
    what(14) = "A-E-1"
    what(15) = "A-E-2"
    what(16) = "A-F-0"
    what(17) = "A-F-1"
    what(18) = "A-F-2"
    what(19) = "B-A-0"
    what(20) = "B-A-1"
    what(21) = "B-A-2"
    what(22) = "B-B-0"
    what(23) = "B-B-1"
    what(24) = "B-B-2"
    what(25) = "B-C-0"
    what(26) = "B-C-1"
    what(27) = "B-C-2"
    what(28) = "B-D-0"
    what(29) = "B-D-1"
    what(30) = "B-D-2"
    what(31) = "B-E-0"
    what(32) = "B-E-1"
    what(33) = "B-E-2"
    what(34) = "B-F-0"
    what(35) = "B-F-1"
    what(36) = "B-F-2"
    what(37) = "C-A-0"
    what(38) = "C-A-1"
    what(39) = "C-A-2"
    what(40) = "C-B-0"
    what(41) = "C-B-1"
    what(42) = "C-B-2"
    what(43) = "C-C-0"
    what(44) = "C-C-1"
    what(45) = "C-C-2"
    what(46) = "C-D-0"
    what(47) = "C-D-1"
    what(48) = "C-D-2"
    what(49) = "C-E-0"
    what(50) = "C-E-1"
    
          For i = 1 To 50
          Do
           Set rng = ActiveSheet.UsedRange.Find(what(i))
           If rng Is Nothing Then
               Exit Do
           Else
               Rows(rng.Row).Delete
           End If
           Loop
           Next i
           
              Rows("1:1").Select
        Selection.Insert Shift:=xlDown
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "LOCATION"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "HAWB"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "DEST"
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "PCS"
        Range("E1").Select
        ActiveCell.FormulaR1C1 = "STATUS"
        Range("A1:E1").Select
        Selection.Font.Bold = True
    Application.ScreenUpdating = True
    The full code was too long, had to delete some but changed the macro to make it work if you try to run it!

    If it's just a simple character addition I'll be pretty darn satisfied
    Last edited by Jsin; 06-23-2010 at 11:00 AM. Reason: Per moderator, my title sucked! Sorry about that though!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1