Search text Value, copy range to sheet

[spyderz]

1. I am trying to search for a text value "NAME" on sheet "Roster" then copy a range starting at the row where "NAME" was found, say "A19:K100" to a sheet already created "Student Info", i will then be able to use this as a template to search for other items in the Roster Sheet.
2. Right now i import a text file, but the file is not always formatted the same, so the formulas throughout the excel workbook won't add up unless i manually change the text document prior to refreshing the data, for co-workers this won't work. So if anyone would be willing to assist me with this, it would be Greatly Appreciated! Thanks again either way!
3. The Workbook Project: http://www.mediafire.com/file/zd9tp9obcnp2od8/JITv2.xlsm
4. A sample of the Data I'm importing: http://www.mediafire.com/file/krlf1r5lgnm245k/TEST ROSTER.txt

 

[Snakehips]

Just for completeness and to give a more general answer to the original question......

Use the Excel MATCH function

The formula =MATCH("NAME",Roster!A:A,0) will return the row number of the first cell, down, in col A that has the specific text NAME

If the text NAME might be in col A or col B ,but not both, due to importing from a text file with inconsistent formatting then you could use..

=IF(ISNA(MATCH("NAME",Roster!A:A,0)),MATCH("NAME",Roster!B:B,0),MATCH("NAME",Roster!A:A,0))

The IF(ISNA(....... alows for the fact that one or other of the MATCH functions will return an #N/A error.

To use the MATCH function when coding with VBA you will want to assign the function result to a variable eg

NameRow = Application.WorksheetFunction.Match("NAME", Sheets("Roster").Range("A:A"),0)

NameRow is then the first row number of your source range for any subsequent transfer to another destination range, whether by copy & paste or transfer of values.

 

[spyderz]

Just for completeness and to give a more general answer to the original question......

Use the Excel MATCH function

The formula =MATCH("NAME",Roster!A:A,0) will return the row number of the first cell, down, in col A that has the specific text NAME

If the text NAME might be in col A or col B ,but not both, due to importing from a text file with inconsistent formatting then you could use..

=IF(ISNA(MATCH("NAME",Roster!A:A,0)),MATCH("NAME",Roster!B:B,0),MATCH("NAME",Roster!A:A,0))

The IF(ISNA(....... alows for the fact that one or other of the MATCH functions will return an #N/A error.

To use the MATCH function when coding with VBA you will want to assign the function result to a variable eg

NameRow = Application.WorksheetFunction.Match("NAME", Sheets("Roster").Range("A:A"),0)

NameRow is then the first row number of your source range for any subsequent transfer to another destination range, whether by copy & paste or transfer of values.

Thanks Again For the help! That completely solved the problem. This is the Final SnS if you will, Working Code For All those who may also be having the same problem as I was. Also is the Search Specific Text, Copy Cell Value to Specific Cell In Range, (not as professional..Becasuse I wrote that Part, but works all the same) Thanks Again Snakes!!

Sub Update_Student_Info()
Dim wsRost As Worksheet
Dim wsStud As Worksheet
Dim wsStar As Worksheet
Set wsRost = Sheets("Roster")
Set wsStud = Sheets("Student Info")
Set wsStar = Sheets("Start Here")
Set wsBody = Sheets("Body Fat")
'On Error GoTo BadRoster  'In case bad or wrong text file and can't find NAME in col A
'get row number of first student name ie 2 rows below occurance of NAME in roster col A
Firstrow = Application.WorksheetFunction.Match("NAME", wsRost.Range("A1:A250"), 0) + 2
wsStud.Range("A6:D6,A11:Z250").ClearContents
wsStar.Range("B4:B27").ClearContents
wsBody.Range("E3:H27,J3:L27,P3:R27,V3:X27").ClearContents
 
'get row number of last student name ie last data in roster col H
Lastrow = wsRost.Range("H65536").End(xlUp).Row
Studrow = 10
With wsRost
For r = Firstrow To Lastrow Step 3  'increment 3 since names 3 rows apart
    Studrow = Studrow + 1
 
 
    'check for trailing comma in what should be 2nd initial column - if yes, = suffix so delete cell & shift data
    If Right$(.Cells(r, 3), 1) = "," Then .Cells(r, 3).Delete Shift:=xlToLeft
    'check for missing 2nd initial - if missing, insert cell & shift data
    If Not Application.WorksheetFunction.IsText(.Cells(r, 4)) Then .Cells(r, 4).Insert Shift:=xlToRight
    ShiftInitial = 0
 
        RankTitle = .Cells(r, 1).Value
        If RankTitle = "" Then RankTitle = "???"
        wsStud.Cells(Studrow, 1).Value = RankTitle
        'populate cells in Student Info
 
        Surname = .Cells(r, 2).Value
        If Not Right$(Surname, 1) = "," Then Surname = Surname & ","
        wsStud.Cells(Studrow, 2).Value = Surname 'surname
        Forename = .Cells(r, 3).Value & " " & .Cells(r, 4).Value 'Forename + initial
 
                    wsStud.Cells(Studrow, 3).Value = Forename    'Forename & initial
                    wsStud.Cells(Studrow, 5).Value = .Cells(r, 5).Value    'SSN
                    wsStud.Cells(Studrow, 6).Value = .Cells(r, 6).Value   'BR
                    wsStud.Cells(Studrow, 7).Value = .Cells(r, 7).Value   'CL
                    wsStud.Cells(Studrow, 8).Value = .Cells(r, 8).Value   'UIC
                    wsStud.Cells(Studrow, 9).Value = .Cells(r, 9).Value   'Rate
 
                    'Username
                    wsStud.Cells(Studrow, 4).Value = .Cells(r + 1, 2).Value & " " & _
                    .Cells(r + 1, 3).Value & " " & .Cells(r + 1, 4).Value
 
Next r
End With
GoTo Out
BadRoster:
    Response = MsgBox("Cannot update Student Info  -  Roster not compatible.", vbOKOnly, "Sorry!")
On Error GoTo 0
Out:
Set wsRost = Nothing
Set wsStud = Nothing
Set wsStar = Nothing
Set wsBody = Nothing
Application.ScreenUpdating = True
Call Insert_Title
End Sub
Sub class_info()
Application.ScreenUpdating = False
        Sheets("Roster").Select
    Rows("1:4").Select
    Selection.Find(What:="CDP:", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("Student Info").Select
    Range("E6").Select
    ActiveSheet.Paste
    Sheets("Roster").Select
    Rows("10:15").Select
    Selection.Find(What:="CIN:", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("Student Info").Select
    Range("A6").Select
    ActiveSheet.Paste
    Sheets("Roster").Select
    Rows("13:19").Select
    Selection.Find(What:="class", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    If ActiveCell.Offset(1, 0).Value = "" Then
    ActiveCell.Offset(2, 0).Select
    Selection.Copy
    Sheets("Student Info").Select
    Range("B6").Select
    ActiveSheet.Paste
    Else
    ActiveCell.Offset(1, 0).Select
    Selection.Copy
    Sheets("Student Info").Select
    Range("B6").Select
    ActiveSheet.Paste
    End If
    Sheets("Roster").Select
    Rows("13:19").Select
    Selection.Find(What:="work", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    If ActiveCell.Offset(1, 0).Value = "" Then
    ActiveCell.Offset(2, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Student Info").Select
    Range("C6").Select
    ActiveSheet.Paste
    Else
    ActiveCell.Offset(1, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Student Info").Select
    Range("C6").Select
    ActiveSheet.Paste
    End If
    Sheets("Roster").Select
    Cells.Find(What:="center", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    If ActiveCell.Offset(1, 0).Value = "" Then
    ActiveCell.Offset(2, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Student Info").Select
    Range("D6").Select
    ActiveSheet.Paste
    Else
    ActiveCell.Offset(1, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Student Info").Select
    Range("D6").Select
    ActiveSheet.Paste
    End If
    Worksheets("Start Here").Select
    End Sub