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]

Please be aware that, for me at least, your links are resulting in Http 404 Object not found error.
Can you perhaps clarify/expand on your required solution.
Are you considering a vba solution?

 

[Jythier]

Links worked for me.

 

[Snakehips]

No joy for me. Either from Mac using Safari or PC using Mozilla Firefox
Seems 'hacked' ? / redirected to thttpd/2.25b29dec2003 Attack of the Repro Men!!!!????

 

[spyderz]

Not really certain why the links are giving some of you trouble, here are the same files uploaded to 4shared rather than mediafire. can access both aswell as mediafire from iphone, pc, mac-X, and linux, let me know if you still have trouble

http://www.4shared.com/file/ThGSaxwJ/JITv2.html
http://www.4shared.com/file/ThGSaxwJ/JITv2.html

excel file

http://www.4shared.com/document/FciA1SHa/TEST_ROSTER.html
http://www.4shared.com/document/FciA1SHa/TEST_ROSTER.html

Text file

 

[spyderz]

Please be aware that, for me at least, your links are resulting in Http 404 Object not found error.
Can you perhaps clarify/expand on your required solution.
Are you considering a vba solution?

was hoping so,it seems the best solution I think. Least thats what I've been searching, rather piecing together, I'm an amateur at best, but i am still searching. i also have the files posted in a forum here if the links still don't work.

http://www.excelforum.com/excel-200...ge-of-cells-to-another-sheet.html#post2620410

 

[Snakehips]

The new links are aok!!!!
Am I right in thinking that whatever the format of a given text file, the names will always be under the cell that contains the header word "Name"? Thus a relative range of say 80 rows down and 11 cols across will capture your required data which you wish to copy to Student Info? It's bedtime here in the UK but I will give it some thought tomorrow and help if I can.

 

[spyderz]

The new links are aok!!!!
Am I right in thinking that whatever the format of a given text file, the names will always be under the cell that contains the header word "Name"? Thus a relative range of say 80 rows down and 11 cols across will capture your required data which you wish to copy to Student Info? It's bedtime here in the UK but I will give it some thought tomorrow and help if I can.

That's exactly what I'm looking for, a real basic search copy value and paste. I truly do thank you for the help. sleep well and i look forward to hearing from you.

 

[Snakehips]

Hi spyderz,

I've looked at your file and see how the problem is caused by inconsistent formatting of the Roster text file.
Firstly, when more or less blank lines in the top rows change, it effectively moves the Excel Roster row that holds the first student name.
Secondly, because Excel uses the spaces in the text file as a delimiter, a student without a second initial in the text file will have their SSN number in same column as other student's 2nd Initial when imported. Subsequent data, to the right, for such a student will also be offset one cell to the left. I hope that makes sense?

I have two possible solutions.....
It is possible to build a sound set of dynamic data in Student Info just using formulas that utilize Standard Excel Functions. However these formulas may seem a bit complex.
(I can provide the solution via formulas to you or anyone else who really feels the need to know!!!)

The preferred solution, I think, is to use vba.

To achieve any solution, there has to be a limit to the inconsistency of the text file. It would be extremely difficult to second guess and cater for too many differences.

So, I have assumed, at this point, the following...
1. The number of rows above the names may vary. .
2. The first name will be 2 rows below the text "NAME" that will be in column A of the imported Roster
3. Subsequent names will be 3 rows below the previous.
3. Civilian Students will not initially have a Ranking/Title
4. The UserName will be in column B,1 row below the surname.
5. The SSN will be a number, no alpha characters at all.
6. The last student will always have some imported data in column H, UIC. *** This is the column that I am using to determine the last entry. We can't use A because if there is a possibility that there will be no Rank/Title for the last student. Also if we use C to F .. it will miscount because of the Total Students etc, text.

So, after you have taken a back-up of your file!!!!..........

Select Student Info Range A1: Z250 and format cells left justify. This is because the import to Roster will likely create a mix of left and right justification that might otherwise transfer to Student Info.

Student Info Cells A10 - J10 ..... Make Column headers ... RATING, *NAME, USERNAME, SSN,................CATEGORY Format cells Grey for contrast. *I am bringing the surname, first name and initial together as one combined name, but they could soon be split if preferred.

If when refreshing the Roster you, currently, click Cancel in the File select dialog box it will error and take you to the VBE!!! This will scare your users to death!! You should always try and prevent any such happenings. It is often harder to ensure code can't end in error than it is to get it to do what you want.

In the VBE, Module1, delete your Sub Refresh Roster. Also, delete or temporarily re-name your Insert State macro in Module2
Copy the code below and paste it to Module1.

Sub Refresh_Roster()
Application.ScreenUpdating = False
 
On Error GoTo Out  'Prevent cancel error
    ActiveWorkbook.RefreshAll
    On Error GoTo 0 'Reset error handling to normal
    Call Update_Student_Info
Out:
On Error GoTo 0
 
End Sub
Sub Update_Student_Info()
 
Dim wsRost As Worksheet
Dim wsStud As Worksheet
 
 
Set wsRost = Sheets("Roster")
Set wsStud = Sheets("Student Info")
 
 
 
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("A11:Z250").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
    ShiftLeft = 0
    ' check for missing 2nd initial - if missing causes a data import shift
    If Not Application.WorksheetFunction.IsText(.Cells(r, 4)) Then ShiftLeft = 1
        RankTitle = .Cells(r, 1).Value
        'Set any missing title to ???
        If RankTitle = "" Then RankTitle = "???"
        wsStud.Cells(Studrow, 1).Value = RankTitle
        'Populate cells in Student Info
        Studname = .Cells(r, 2).Value & " " & .Cells(r, 3).Value
       
        If ShiftLeft = 0 Then Studname = Studname & " " & .Cells(r, 4).Value
            wsStud.Cells(Studrow, 2).Value = Studname
               
                    wsStud.Cells(Studrow, 4).Value = .Cells(r, 5 - ShiftLeft).Value
                    wsStud.Cells(Studrow, 5).Value = .Cells(r, 6 - ShiftLeft).Value
                    wsStud.Cells(Studrow, 6).Value = .Cells(r, 7 - ShiftLeft).Value
                    wsStud.Cells(Studrow, 7).Value = .Cells(r, 8 - ShiftLeft).Value
                    wsStud.Cells(Studrow, 8).Value = .Cells(r, 9 - ShiftLeft).Value
                    wsStud.Cells(Studrow, 3).Value = .Cells(r + 1, 2).Value & " " & _
                    .Cells(r + 1, 3).Value & " " & .Cells(r + 1, 4).Value
                    
Next r
End With
GoTo Out
BadRoster: ' Message and abort if bad NAME
    Response = MsgBox("Cannot update Student Info  -  Roster not compatible.", vbOKOnly, "Sorry!")
On Error GoTo 0
 
Out:
Set wsRost = Nothing
Set wsStud = Nothing
 
End Sub
 
Sub Insert_State()
'to replace ??? with civilian title
'Get last data row num and -8 = no of students listed
lastr = Sheets("Student Info").Range("A66536").End(xlUp).Row - 8
With ActiveSheet
'Go down list and display input box if ??? detected
For r = 2 To lastr
If Mid$(.Cells(r, 1).Text, 1, 3) = "???" Then
.Cells(r, 1).Select
Sheets("Student Info").Cells(r + 9, 1).Value = InputBox("ENTER CIVILIAN TITLE" & Chr$(13) & "USING CAPITAL LETTERS", "Please Input Civilian Title")
'Sheets("Roster").Cells(1,1).Value = Sheets("Student Info").Cells(r + 9, 1).Value
End If
Next r
End With
End Sub

It includes a fail-safe Refresh_Roster that now refreshes the text file calls the new code - Sub_Update_Student_Info.
Update _Student_Info will populate the sheet with values not formulas.
Your current Eter_State macro is confused by the mess of data and is asking for a lot of erroneous input.
So I have also edited your Insert_State code. which hopefully you will see as an improvement.**

A lot of current links to student names, etc are a bit confused to say the least.
Student Info should now be your sole reference to this data!!! for linking to other sheets.
Because there are no links within Student Info you can actually go in there and make any edits should you need. The edits will reflect in all linked sheets but not after you refresh Roster.


**On your Start Here sheet, enter the formula
=IF('Student Info'!A11="","",'Student Info'!A11&" '&'Student Info'!B11) into cell A2
and copy it down for your 25ish rows. It is not perhaps your actual required text for those cells but will serve as an example.

When Student Info updates it now sets any missing (civilian) title as ??? which will be seen as the first three characters in the Start Here name cell.
If you select the cells A2 to A25? and apply conditional formatting with the rule =LEFT($A2,3)="???" & format cell to pik then all un-titled civilians in Start Here will show as pink cells.
Now when you click INPUT CIV TITLES it will find any ??? in Student Info and request a proper Title. The input will actually be made in Student Info, not Roster as now. This will then reflect in Start here and all other linked sheets.
You can test this by editing one or two rankings, directly in Student Info, to ???. Then go try INPUT CIV TITLES.

I have just realized what Clean Roster does.
If my assumption 6 above holds good then we can leave things as they are in which Clean Roster serves no purpose? Otherwise, we could call Clean Roster immediately after text import and this would allow a valid value of lastrow using col B for name?
I any event, add Application.ScreenUpdating = False as the first line of the Clean Roster code and this will eliminate the flicker as it processes.

You may consider hiding the Roster and Student Info sheets as the user will not need to access them? All will function with them hiden and they cannot then be messed with.
Also. Protect Start here but allow select locked cells. Then no one can mess with formulas or amend the button's macros.

If you prefer, you can send me a PM and I will mail you back my trial file.

I hope that this is of some value.
All the best

Tony

 

[Snakehips]

Why is it I only spot the typos after I'm timed out of editing??
SHOULD STILL MAKE SENSE