Macro Filter
How to write a Excel Macro to filter textfile?
I am not an expert in programming, i am seeking help if anyone know how to manipulate the textfile data below to split the data column by column?
Textfile
REC ID DATE TIME STATION CODE CLASS LEVEL
MESSAGE
__________________________________________________________________________
13976 17/04/2009 23:48:21 BP3000 MIS50002 EventLog Evt
Change to Productive State
13975 17/04/2009 23:48:20 Diverter DIV25032 Category X-NA Wrn
Diverter Stopper sensor cannot detect BGA strip!
Excel
13976 17/04/2009 23:48:21 BP3000 MIS50002 EventLog Evt Change to Productive State
13975 17/04/2009 23:48:20 Diverter DIV25032 Category X-NA Wrn Diverter Stopper sensor cannot detect BGA strip!
Assuming that you want each pair of data rows converted to eight columns on one line and to remove blank lines:
Copy and paste the following into the ThisWorkbook window in the VBAProject
Sub SplitSelection()
‘Selection must be a one column range
Dim sCell() As String, oCell As Object
Dim iSelectionRow, iSelectionRows, iRow
Dim iColOffset, iSelectionColumn As Long
iSelectionRow = Selection.Row
iSelectionRows = Selection.Rows.Count
iSelectionColumn = Selection.Column
‘Split into 7 cells and move next row to 8th cell
For Each oCell In Selection.Cells
Select Case (oCell.Row – iSelectionRow) Mod 3
Case 0
sCell = Split(oCell, ” “, 7)
For iColOffset = 0 To 6
Cells(oCell.Row, oCell.Column + iColOffset) = sCell(iColOffset)
Next
Case 1
Cells(oCell.Row – 1, oCell.Column + 7) = oCell
Cells(oCell.Row, oCell.Column) = “”
Case 2
End Select
Next
‘Delete blank rows
iRow = iSelectionRow
Do While (iSelectionRow + iSelectionRows) > iRow
If Cells(iRow, iSelectionColumn) = “” Then
Cells(iRow, iSelectionColumn).EntireRow.Delete
iSelectionRows = iSelectionRows – 1
Else
iRow = iRow + 1
End If
Loop
End Sub
Select the rows you want to convert and run the macro.
Let me know what other help you need using the macro.
|
|
Close Up Macro Filter Set for Minolta Dimage 5 7 7i 7Hi A1 A2 A200 $27.27 |
|
|
Close Up Macro Filter Set for Canon EF 28-105mm USM LENS $29.29 |
|
|
m 77mm MACRO CLOSE UP Lens 4 Filter Kit Set +1+2+4+10 $119.54 |
|
|
m~62MM Close Up Macro +1+2+4+10 Filter Kit for Canon Lens $16.42 |
|
|
46mm M46 Close-Up +1 Macro Close Up No.1 Filter Green L $4.07 |
|
|
Telephoto + wide macro lens + UV CPL FLD filter kit + ring for Minolta Z3 Z5 Z6 $46.99 |
|
|
49mm M49 Close-Up +1 Macro Close Up No.1 Filter Green L $4.97 |
|
|
m 52mm Macro Close-Up +1 +2 +4 +10 Close Up Filter Kit $111.92 |
|
|
52mm M52 Close-Up +1 Macro Close Up No.1 Filter Green L $4.28 |
|
|
m 62MM Macro Close-Up +1 +2 +4 +10 Close Up Filter Kit $16.42 |
|
|
52 52mm Macro Close-Up +1 +2 +4 +10 Close Up Filter Kit $11.93 |
|
|
62 62mm Macro Close-Up +1 +2 +4 +10 Close Up Filter Kit $16.42 |
|
|
m close up set 72mm MACRO CLOSE UP Lens 4 Filter Kit +1+2+4+10 $20.65 |
|
|
m 72mm MACRO CLOSE UP Lens 4 Filter Kit Set +1+2+4+10 $120.65 |
|
|
55mm M55 Close-Up +1 Macro Close Up No.1 Filter Green L $4.78 |
|
|
58mm M58 Close-Up +1 Macro Close Up No.1 Filter Green L $5.18 |
|
|
62mm M62 Close-Up +1 Macro Close Up No.1 Filter Green L $5.21 |
|
|
67mm M67 Close-Up +1 Macro Close Up No.1 Filter Green L $5.31 |
|
|
72mm MACRO CLOSEUP Lens Filter Kit 1+2+4+8 for Nikon D7000 D3100 D5100 D3s D3000 $24.98 |
|
|
mint PRINZ 52mm MACRO CLOSE-UP FILTER SET Japan CASE for CANON NIKON SONY PENTAX $7.99 |
|
|
Soft fisheye macro filter UV white balance lens cap for Pentax K20D K200D K2000 $18.99 |
|
|
MANDEE Canon EOS to Canon FD Macro Converter Lens Adapter Filter Kit Brand New $0.99 |
|
|
77mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $4.78 |
|
|
67mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $4.38 |
|
|
62mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $4.38 |
|
|
49mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $4.98 |
|
|
52mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $3.78 |
|
|
55mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $3.78 |
|
|
58mm Camera Lens Close-Up +3 Macro Close Up No 3 Filter $5.78 |
|
|
ZEIKOS 4 pc CLOSE UP MACRO FILTER KIT – 58 mm +1 +2 +4 +10 $9.99 |
|
|
58mm Close Up Macro +1+2+4+10 Filter Kit For Canon EOS 5D 550D 600D Rebel T3i XS $8.99 |
|
|
72mm MACRO CLOSE UP Set Lens Filter 1+2+4+8 for Canon EOS 60D 1000D 500D 7D 50D $24.98 |
|
|
ZENZA BRONICA 6X6 SQ MACRO 110MM f4 ZENZANON PS LENS & filter 11103806 $299.95 |
|
|
Tiffen 72mm Close-up Filter Set with +1, +2 +4 Macro Lenses. $40.00 |
|
|
Multicoated Macro Close- Up Lens 3 PCs Filter Kit +1+2+4 For 49mm Film & Digital $24.74 |
|
|
72mm Zoom MACRO CLOSE UP Lens Filter 1+2+4+8 for Canon EOS 1100D 600D 550D 1000D $24.98 |
|
|
49mm Photax Close Up +1 filter MACRO Still Life $12.47 |
|
|
Close Up Macro Lens Filter for Sony SLT-A33L A33 Translucent Mirror 55mm Ø $28.15 |
|
|
Close Up Macro Lens Filter for Sony SLT-A35K A35 Translucent Mirror 55mm Ø $28.15 |
|
|
Opteka 55mm HD Lens Filter Kit for Sony 100mm Macro Lens $12.95 |
|
|
Close Up Macro Filter KIT FOR Canon EF-S 17-85mm IS USM $27.95 |
|
|
55MM 10 X MACRO FILTER WITH SOFT POUCH $14.92 |
|
|
CloseUp MACRO Filter Lens Kit FOR Canon EOS 40D 30D 1Ds $19.95 |
|
|
Sigma DL Aspherical Macro 28 mm – 200 mm F/3.5-5.6 Lens FOR CANON W/ Filters!! $109.99 |
|
|
VIVITAR 55MM macro close up +1 +2 +4+10 filter KIT FOR SONY 18-70mm 18-70mm $12.99 |
|
|
UV ULTRAVIOLET FILTER LENS PROTECTOR FOR SIGMA 24mm & 28mm F1.8 EX DG ASP Macro $11.95 |
|
|
Konica Mount 1:4.5 f=70-210mm Macro Zoom Lens with 52mm filter size w/Hard case $15.00 |
|
|
58mm Tele 2x+Wide 0.45x+Flash +Macro+Filter kit For Canon EOS 550d 7d 500d 1000d $124.91 |
|
|
Tamron AF 70 300mm F 4 5 6 Di LD Macro 1 2 Model A17S filter 62mm Sony.d $154.99 |
|
|
49mm +1 +2 +4 & MACRO +10 CLOSEUP LENS FILTERS SET KIT $19.99 |
|
|
52mm +1 +2 +4 & MACRO +10 CLOSEUP LENS FILTER SET KIT $19.99 |
|
|
55mm +1+2+4 & MACRO +10 CLOSEUP LENS FILTER 55 mm $14.99 |
|
|
55mm +1 +2 +4 & MACRO +10 CLOSEUP LENS FILTER SET KIT $19.99 |
|
|
58mm +1 +2 +4 & MACRO +10 CLOSE-UP LENS FILTER SET KIT $19.99 |
|
|
Zeikos 7 52mm MACRO +UV/FLD/CPL Filter for Nikon Camera $36.95 |
|
|
Zeikos 7 58mm MACRO +UV/FLD/CPL Filter for Canon Camera $39.95 |
|
|
Zeikos 7 58mm MACRO +UV/FLD/CPL Filter for Nikon Camera $37.95 |
|
|
Zeikos 7 52mm MACRO +UV/FLD/CPL Filter for Canon Camera $39.95 |
|
|
Zeikos 7 58mm MACRO +1+2+4+10 UV/FLD/CPL Filter +Tripod $39.95 |
|
|
Zeikos 7 52mm MACRO +1+2+4+10 UV/FLD/CPL Filter +Tripod $39.95 |
|
|
Zeikos 7-piece 58mm MACRO 4 +1+2+4+10 UV/FLD/CPL Filter $31.95 |
|
|
Zeikos 7-piece 52mm MACRO 4 +1+2+4+10 UV/FLD/CPL Filter $31.95 |
|
|
Zeikos 7 62mm MACRO +UV/FLD/CPL Filter for Nikon Camera $43.95 |
|
|
Zeikos 7-piece 62mm MACRO 4 +1+2+4+10 UV/FLD/CPL Filter $38.95 |
|
|
Zeikos 7 62mm MACRO +1+2+4+10 UV/FLD/CPL Filter +Tripod $47.95 |
|
|
HD Digital 52mm Close up Macro +1+2+4+10 4pcs Filter Kit for Nikon Pentax Lens $13.95 |
|
|
MACRO Filter Kit FOR Nikon D40 D50 D700 D60 D80 D40x D90 18-55mm 55-200mm lens $13.95 |
|
|
HD Digital 10x Close-Up 4 Filter Macro Kit 52mm for Canon EF 135mm f/2.8 Lens $13.95 |
|
|
52mm Close up Macro +1+2+4+10 Filter Kit Set for AF-S 55-200mm Nikon/Nikkor lens $13.95 |
|
|
52mm Close up Macro +1+2+4+10 Filter Kit for AF-S DX Nikon/Nikkor 18-55mm lens $13.95 |
|
|
HD Digital 52mm Close up Macro +1+2+4+10 10x SLR DSLR Camera Lens Filter Kit Set $13.95 |
|
|
52mm Close up Macro +1+2+4+10 Filters for AF-S DX Nikon/NIKKOR Normal 50mm f/1.2 $13.95 |
|
|
52mm Close up Macro +1+2+4+10 Filter Kit Set for AF-S 55-200mm Nikon/Nikkor lens $13.95 |
|
|
52mm Macro Close Up Filter Kit for Canon EF 50mm f2.5 EF 55-200mm EF-S 60mm NEW! $13.95 |
|
|
52mm MACRO CLOSE UP Lens Filter Kit for Pentax K2000 K-m K-x K20D K200D Kx K7 Kr $13.95 |
|
|
B&W 62mm Macro CLOSE-UP 1 NL NL1 Filter 62 mm 65075864 $34.95 |
|
|
Zeikos 7 72mm MACRO +UV/FLD/CPL Filter for Nikon Camera $49.95 |
|
|
Zeikos 7-piece 72mm MACRO 4 +1+2+4+10 UV/FLD/CPL Filter $44.95 |
|
|
B&W 62mm Macro CLOSE-UP 2 NL2 Lens Filter 62 mm B + W $34.95 |
|
|
Zeikos 7 67mm MACRO +UV/FLD/CPL Filter for Canon Camera $48.95 |
|
|
Zeikos 7-piece 67mm MACRO 4 +1+2+4+10 UV/FLD/CPL Filter $40.95 |
|
|
Zeikos 7 67mm MACRO +1+2+4+10 UV/FLD/CPL Filter +Tripod $49.95 |
|
|
Zeikos 7 72mm MACRO +UV/FLD/CPL Filter for Canon Camera $52.95 |
|
|
Zeikos 7 72mm MACRO +1+2+4+10 UV/FLD/CPL Filter +Tripod $53.95 |
|
|
Zeikos 7 67mm MACRO +UV/FLD/CPL Filter for Nikon Camera $45.95 |
|
|
Nikon 28mm w/Filter 52mm Macro $69.99 |
|
|
Zeikos 7-piece 77mm MACRO 4 +1+2+4+10 UV/FLD/CPL Filter $48.95 |
|
|
Zeikos 7 77mm MACRO +UV/FLD/CPL Filter for Canon Camera $56.95 |
|
|
Zeikos 7 77mm MACRO +1+2+4+10 UV/FLD/CPL Filter +Tripod $56.95 |
|
|
Zeikos 7 77mm MACRO +UV/FLD/CPL Filter for Nikon Camera $53.95 |
|
|
Wide macro lens + close up + filter kit UV CPL FLD for Canon G10 G11 G12 $91.99 |
|
|
52mm Macro Close-Up +10 Lens Filter for NIKON 18-55mm D40 D40x D50 D60 D90 D700 $8.00 |
|
|
VCCKIT FOR SONY A290 A390 A500 A33 WIDE ANGLE MACRO TELE LENS FILTERS KIT Hood + $67.00 |
|
|
52mm Close up Macro +1 +2 +4 +10 Filter Kit for NIKON D3000 D5000 18-55mm LF57 $14.98 |
|
|
AGFAPHOTO 58mm Macro Close Up Filter set Kit STACKABLE +1+2+4 +10 = +17 LENS $17.94 |
|
|
Takumar Pentax Macro 100mm f4 Lens & Caps Case UV Filter Screw Mount $103.50 |
|
|
77mm MACRO CLOSE UP Lens 3PC Filter 1+2+4 $44.99 |
|
|
Macro Closeup 4PC Lens Filter for Canon EOS 60D 50D 40D $27.99 |
|
|
0.43x Wide Angle w/Macro Lens + UV CPL FLD Filter Kit for Canon 600D 550D 1100D $57.49 |
|
|
Filter Kit+Macro Set+Hood+Mini Tripod Lens For Canon HQ $53.81 |
