Find Function within a Macro

[Roller]

Hello Mr. Excel Message Board viewers,

I was wondering if someone could suggest the best way to look for a specific text string in a range of cells and then copy the contents of that cell into a specific cell in the next worksheet.

Given conditions:
Sheet1 contains imported data. I am using the import function to automatically update each time the data file is updated. The number of rows varies each time (A1:A????). Data only populates column A. Data in each row of column A can be blank or will be text up to 100 characters. The text string ("_1_1_GL") that I am looking for will only populate one row per file (again which row it's in is variable each time).

Sheet2 contains the results of this macro. I would like it to copy the contents of cell Sheet1!A?? to Sheet2!A1.

Other info... There are 30 conditions I need to search the text for but only one condition will be present each time a data file is created. Condition 1 results will go in Sheet2!A1, Condition 2 will go in Sheet2!A2, etc. I am certain I can replicate the code for each condition but I need a nudge to get going. Any help is appreciated. Thanks a million.

 

[shg]

One way:

Sub x()
    Dim rFind       As Range
    Dim vWhat       As Variant
 
    For Each vWhat In Array("_1_1_GL", "_1_2_GL", "_1_3_GL")
        Set rFind = Sheet1.Range("A:A").Find(What:=vWhat, _
                                             LookIn:=xlValues, _
                                             LookAt:=xlWhole, _
                                             MatchByte:=False)
        If Not rFind Is Nothing Then
            rFind.Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next vWhat
End Function

 

[Roller]

Thank you. I tried it but the macro did not return any results, at first. Then I noticed that the text string ("_1_1_GL" and so on) is actually part of a larger text string. The actual text is: "@ com pause '_1_1_GL records |'+1139" in the data file. You were correct in your assumption that each iteration was _1_2, _1_3 and so on, but the number at the end will be different. Is there a way to edit this so that it only looks for the _1_1_GL portion of the text string?

 

[shg]

Change xlWhole to xlPart.