1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro : Find > Copy > Find > Copy > Find > Copy > Print all found in File

Discussion in 'Business Applications' started by adnanjav, Oct 9, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    Hi Techies ,

    I have been looking to find a macro to help me sort out my excel file. I am new to macros so the one I am looking for has to look for a text , copy another column of that row(eg lets say E11) where text is found , find this text (E11 text) , copy another column of a row where E11 is found ( say F21 ) , find F21 and copy another coulmn of a row where F21 was found and print all these ( text , E11 , F21 etc. ) on a new sheet in a single row.

    examples are all highlighted fields in the attached file.

    Can someone help me with creating a macro!!! ?
     
  2. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    attached now!
     

    Attached Files:

    • 1.rar
      File size:
      72.4 KB
      Views:
      29
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,528
    First Name:
    Hans
    A csv file doesn't contain any formatting so your attachment is of no use to illustrate your needs.
    Attach an Excel file
    and DO NOT FORGET to mention the Excel version
     
  4. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    Hi Hans,

    Thanks for the reply. I shall be getting a csv as an output with the same format each time.

    The logic i require is to search for a sting in column A , if the string is found in A10 , look at the row 10 and extract E10 , search column B with text found in E10 , if found in B20 , look up row 20 and copy G20 and I20. Search for G20 in column B , if found in lets say row 25 at B25 , look at row 25 and copy I25.

    In the end Print all found and search A10 , E10 , G 20 , I 20 , I 25 in a new file.

    Using Excel 14.0.6023.1000 (64bit)
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,528
    First Name:
    Hans
    I suggest the following links to help you on the way:
    http://www.cpearson.com/Excel/Topic.aspx
    one of these topics will take you to this link: http://www.cpearson.com/Excel/FindAll.aspx

    I'm sure, with the sample files which are sometimes present you will be able to sort it out.

    If you have something and need assistance I'll gladly help further on.

    Think of a macro of a series of commands that are exceuted one after another, so try and transalte the actions you need to instructions in a amcro and it will work.
     
  6. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    Hi ,

    I haven't been successful in my attempts. However i have managed to get the file in excel version 14.0.6023.1000 (64 bit).

    Looking forward to you help!

    Here is what I want.
    Search "987654000000002" which is in A10. Copy this entire row and print in sheet2 , and search for K10 "67109860" in the file. Print all rows K10 is found to sheet2 ( like row 29 , 30 , 31 and 65 ) , and search for K29 (first row where K10 is found) in the file. Print all rows K25 is found to sheet2.

    Thats all.

    so Sheet2 has row which had value of A10 , K10 and K29. However these row numbers may change depending on initial search parameter.
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,528
    First Name:
    Hans
    With Print you mean paste?
    What happens if the value in is already presnet in Sheet 2 because the first selection in column A is already there?

    A10 = 987654000000002 and K10=67109860
    The row numbres is no problem but I do need to know this if you need help
     
  8. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    Print means paste. The values can be repeated.
     
  9. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    :D:D:D
     

    Attached Files:

  10. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    Thanks mate. I saw the output! I have two more queries though (I am quite new at this) :

    1) How do I search for other instances in column A such as "987654000000004 or 987654000000005" ?

    2) Now by searching 987654000000004 , assuming it is in row 20 , I need to copy this complete row and paste in sheet2 , and search for K20 and and paste all rows with value of K20 in sheet2.
    Moreover the value of K20 found in column B (lets say it is found in row 32) , I have to search for K32 , copy entire row and paste in sheet 2 , also copy all rows containing value of K32 and paste in sheet2.

    For clarification i have highlighted all rows that should be copied and pasted in sheet2 when "987654000000004 " is searched. The parameter for search should be configurable so any string in Column A can be searchable while behavior of copying of rows for K?? columns ( ?? is depending on which row is searchable parameter found in)
     

    Attached Files:

  11. adnanjav

    adnanjav Thread Starter

    Joined:
    Oct 9, 2012
    Messages:
    7
    Help ...... !!!!!
     
  12. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Try this. (Based on Garf13LD's code.)

    Code:
    Sub test()
        Dim rw As Range
        Dim rng As Range
        Dim startCell As Range
        Dim str1 As String, str2 As String, str3 As String
        Dim i As Long
        Set rng = Worksheets("Sheet1").UsedRange
        Worksheets("Sheet2").Cells.Delete
        Set startCell = Worksheets("Sheet2").Range("A1")
        rng.Rows(1).Copy startCell
        Set startCell = startCell.Offset(1)
        str1 = InputBox("Enter String")
        For Each rw In rng.Rows
            
            If Not rw.Find(str1) Is Nothing Then
                If str2 = "" Then str2 = Intersect(Worksheets("Sheet1").Range("K:K"), rw)
                rw.Copy startCell
                Set startCell = startCell.Offset(1)
            End If
        Next
        For Each rw In rng.Rows
            If Not rw.Find(str2) Is Nothing Then
                If str3 = "" Then str3 = Intersect(Worksheets("Sheet1").Range("K:K"), rw)
                If str2 = str3 Then str3 = ""
                rw.Copy startCell
                Set startCell = startCell.Offset(1)
            End If
        Next
        For Each rw In rng.Rows
            If Not rw.Find(str3) Is Nothing Then
                rw.Copy startCell
                Set startCell = startCell.Offset(1)
            End If
        Next
    End Sub
    If this doesn't work then you should try to explain the real meaning behind the request.

    Jimmy
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1071951

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice