On Friday, June 6, 2014 4:06:34 PM UTC-6, Nila wrote:
Hello Everyone,



I need to do the following functions using excel vba



1. Ask user to select the text file using userform

2. Load the text file into excel (delimit by ',' and '=') - (sheet name :test)

3. Delete first 14 lines

4. Create a new sheet -( sheet name : sheet2)

5. Search file for ##RETENTION_TIME and ##NPOINTS, Copy the value of ##RETENTION_TIME to the new sheet (sheet1) based on ##NPOINTS. E.g: If ##RETENTION_TIME = 0.6 and ##NPOINTS = 10. 0.6 should be copied to cells from A1 to A10 in the sheet1. This should be repeated until it reaches the last row.



Following is the source code I have return for the above functionality. The code works fine till step 4. Step 5 is done by "sub sort". This is where I am getting a compile error "Sub or function not defined". I am new to excel vba, I am not able to narrow down the issue further. Could anyone help me on this issue please? TIA.

Code

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Dim fileOpen As Variant



fileOpen = Application.GetOpenFilename("All Files(*.*),*.*")

If fileOpen = False Then Exit Sub

Workbooks.OpenText (fileOpen)

Range("A1").Select

Rows("1:14").Select

Selection.Delete Shift:=xlUp

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _

"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True



Call sort



Close fileOpen

ActiveWindow.Close False

Application.ScreenUpdating = True



End Sub



Sub sort()



Dim x As Integer

Dim y As Integer

Dim erow As Long



y = 10

x = 2

Sheets.Add after:=Sheets(Sheets.Count)

Do While Cells(1, x) < ""

If Cells(1, x) = "##RETENTION_TIME" Then

Worsksheets("test").cell(1, x).Copy

Worksheets("sheet1").Activate

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Paste destindation:=Worksheets("sheet1").Range("erow" & ":" & "A" & "y")

End If

Worksheets("test").Activate

x = x + 1

Loop



End Sub

Thanks Gary and Claus, but still i am not able resolve the issue. Please find the input and output files here. Both of them are sample files the file might contain more than 10K data.

input
https://drive.google.com/file/d/0B7S...it?usp=sharing

output

https://docs.google.com/spreadsheet/...UE&usp=sharing
https://drive.google.com/file/d/0B7S...it?usp=sharing