none
Want to change search from column C to R and V in Macro RRS feed

  • Question

  • Presently I can do a  search for numbers in column C.  I want to change the Macro so I can do a search for numbers in columns R and V too.  What do I need to do to my Macro to incorporate the extra 2 columns or even have separate macros and separate sheets?

    How is works is you press the big Gray button  and a box appears that says select ranges.   You then high lite with your mouse the cells in C:2  E:2 through C:12  E:12.  Then press okay.  After it finds the different combinations you click next to find the next one.  I would love for the Macro to take all the different ones it finds and place them on a separate sheet so I can see them all at once.  This will also save me time from having to copy and paste them to a new sheet.  If this can be done I would need to keep them in the same columns on the other sheet(s) C R V due to the conditional formatting (I really do not want to have to change all the column numbers  LOL)  The data I will be placing in R and V have a lot of color coding formatting not showed in this example.   I would also like to view 10 rows up and below what it finds.  So, if I want to find lets say 24642 in column C,  R or V and it finds them in rows 21,22,23,24,25 it will also display rows 11-20 and 26-35.

    Thank you so much !!!!

    Here is a snap shot and the current Macro

    Image

    Image

    Sub test()
        Dim myPtn As Range, r As Range, x, myTxt, mymatch As Range
        Dim ff As String, i As Long, y, flg As Boolean, myAreas As Areas
        Dim Match
        Columns("c:e").Borders.LineStyle = xlNone
        Columns(6).ClearContents
        Set myAreas = Application.InputBox("Select the pattern range(s)", Type:=8).Areas
        For Each myPtn In myAreas
            myTxt = myPtn(1).Value
            Set r = Columns(3).Find(myTxt, , , 1)
            If Not r Is Nothing Then
                ff = r.Address
                Do
                    x = Evaluate(r.Resize(myPtn.Rows.Count, 2).Address & "=" & myPtn.Address)
                    For i = 1 To 2
                        y = Filter(Application.Transpose(Application.Index(x, 0, i)), False)
                        If UBound(y) <> -1 Then flg = True: Exit For
                    Next
                    If Not flg Then
                        If mymatch Is Nothing Then
                            Set mymatch = r.Resize(myPtn.Rows.Count, 3)
                        Else
                            Set mymatch = Union(mymatch, r.Resize(myPtn.Rows.Count, 3))
                        End If
                        r.Resize(myPtn.Rows.Count, 3).BorderAround Weight:=xlThick
                        r.Offset(, 3).Value = "x"
                    End If
                    Set r = Columns(3).FindNext(r): flg = False
                Loop Until ff = r.Address
            End If
        Next
        MsgBox IIf(mymatch Is Nothing, "No match", Replace(mymatch.Address, ",", vbLf)), _
        , IIf(mymatch Is Nothing, "Not ", "") & "Found"
        If Not mymatch Is Nothing Then mymatch.Select
        

    End Sub

    Private Sub CommandButton1_Click()
    Dim c
    Dim firstAddress As String
     With Worksheets("Filter").Range("F2", Range("F" & Rows.Count).End(xlUp))
            Set c = .Find("x", LookIn:=xlValues)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Offset(, -3).Resize(1, 3).Select
                    Set c = .FindNext(c)
                    If MsgBox("Next Match?", vbYesNo) = vbNo Then Exit Sub
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    End Sub

    Saturday, December 13, 2014 5:18 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    • Proposed as answer by Fahime71 Tuesday, December 16, 2014 6:54 AM
    • Unproposed as answer by Fahime71 Tuesday, December 16, 2014 6:54 AM
    Monday, December 15, 2014 6:43 AM
  • Hi Larbec,

    >> I want to change the Macro so I can do a search for numbers in columns R and V too

    You could union columns through Union method, then search the specify data.

    >> I would love for the Macro to take all the different ones it finds and place them on a separate sheet so I can see them all at once

    I suggest that you could copy specify range (all) to other worksheet through Range.Copy method with Destination parameter, then do the search logical in the original worksheet, if it matches, clear the content for target worksheet, at last, the target worksheet will just have the different data.

    You could base on these suggest to try it with your code by yourself.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 16, 2014 6:53 AM
    Moderator
  • Thanks for the reply Starain, I went and read up on the Union and range copy but I am not sure what to do and where to put the code.  I had a lot of help writing the code I placed on here and I am not familiar with writing Macros.  Can you please show me where it goes and what I need to do so next time I will know?

    Thank you so much!!!! You all are the best and smartest folks I know

    Wednesday, December 17, 2014 7:16 PM
  • Hi Larbec,

    You could do it in the click event of button, it is similar to you did. What I mean is that you need to modify the original code to meet the new requirement.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Larbec Thursday, December 18, 2014 2:27 PM
    • Unmarked as answer by Larbec Thursday, December 18, 2014 2:27 PM
    Thursday, December 18, 2014 9:24 AM
    Moderator
  • Hi Starain, I do not understand how to do any of this I'm attending classes but looking for someone to either change it for me or give me step by step instructions how to change it myself. I'm a newbie
    Thursday, December 18, 2014 2:29 PM
  • Hi Larbec,

    You could do it in the click event of button, it is similar to you did. What I mean is that you need to modify the original code to meet the new requirement.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    I have been messing around with this for hours.  Are you saying i should delete my code I have and in its place put this?

    Public Sub CopyRows() 
        Sheets("Sheet1").Select 
        ' Find the last row of data 
        FinalRow = Cells(Rows.Count, 1).End(xlUp).Row 
        ' Loop through each row 
        For x = 2 To FinalRow 
            ' Decide if to copy based on column D 
            ThisValue = Cells(x, 4).Value 
            If ThisValue = "A" Then 
                Cells(x, 1).Resize(1, 33).Copy 
                Sheets("SheetA").Select 
                NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
                Cells(NextRow, 1).Select 
                ActiveSheet.Paste 
                Sheets("Sheet1").Select 
            ElseIf ThisValue = "B" Then 
                Cells(x, 1).Resize(1, 33).Copy 
                Sheets("SheetB").Select 
                NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
                Cells(NextRow, 1).Select 
                ActiveSheet.Paste 
                Sheets("Sheet1").Select 
            End If 
        Next x 
    End Sub 
     
    

    Do I go through all the numbers in column C and put them in individually in the place of "A"

     If ThisValue = "A" Then 

    Friday, December 19, 2014 6:58 AM
  • see if this makes better sense what I am trying to do, the last file did not upload correctly

    Instructions:

    When you open the file you will see under column C the numbers 2 10 2 in rows C:2 C:3 C:4. I am looking for all the number sequences down column C that have the 2 10 2 in them

    Click on the button labeled "Find Patterns"
    That brings up a box that says select range(s) you will high lite C:2 to E:2 and drag down to C:4 E:r and then press k"

    This will begin the search. When it finds the answers it will show you a location that the next set of numbers ( 2 10 2) are in $C$17:$E$22
    click on "ok" this brings you to that location

    Now, for what I want I need help with. I want to be able for the Macro to copy and paste all the locations it finds and place them on the next sheet with 10 rows above and 10 rows below (info from B:J)

    If this can not be done easily then I would like the ability to pause the search and copy the location to another sheet manually and then start where it left off. Presently every-time I go to copy and paste, I have to stop it and start all over again. Ideal would be to copy and paste to the next sheet which will save an enormous amount of time

    This should make better sense LOL, sorry for the confusion and wasted time

    Number Search

    Saturday, December 20, 2014 6:41 AM