Excel VBA help: Text file formatting
Hi Nila,
Am Fri, 6 Jun 2014 22:31:16 -0700 (PDT) schrieb Nila:
I'm expecting my output file to be (Sample not the actual file)
0.6 30.92 269
0.6 30.99 317
0.6 32.59 302
0.6 33.26 337
0.6 34.86 492
0.6 34.99 316
0.6 36.66 319
0.6 37.79 295
0.6 38.92 269
0.6 38.99 262
1.1 30.39 157
1.1 31.52 221
1.1 32.72 321
1.1 33.26 263
1.1 34.46 317
1.1 35.52 289
1.1 36.66 361
1.1 37.85 313
1.1 37.99 157
1.1 39.65 246
try:
Sub Sort()
Dim cRet As Range, cNo As Range
Dim LRow As Long
Dim FirstAddress As String
Dim myCnt As Long
Dim First As Range
Dim ArrIn As Variant
'Modify sheet names
With Sheets("Test")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set cRet = .Range("A1:A" & LRow).Find("##RETENTION_TIME", _
LookIn:=xlValues)
If Not cRet Is Nothing Then
FirstAddress = cRet.Address
Do
myCnt = Trim(Mid(cRet.Offset(2, 0), InStr(cRet.Offset(2, 0),
"=") + 1, 99))
Set First = Sheets("Sheet1").Cells(Rows.Count,
1).End(xlUp).Offset(1, 0)
First.Resize(rowsize:=myCnt) = Trim(Mid(cRet, InStr(cRet,
"=") + 1, 99))
ArrIn = cRet.Offset(4, 0).Resize(rowsize:=myCnt)
First.Offset(, 1).Resize(rowsize:=myCnt) = ArrIn
Set cRet = .Range("A1:A" & LRow).FindNext(cRet)
Loop While Not cRet Is Nothing And cRet.Address < FirstAddress
End If
End With
Sheets("Sheet1").Columns("B").TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Comma:=True, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|