­ã´£³¡ªL

Sub TEST()
Dim xPath$, xF$, xS As Worksheet, xEnd As Range, xR As Range, TT, T, N$, C%
xPath = ThisWorkbook.Path & "\"
Set xS = ThisWorkbook.Sheets("¤u§@ªí1")
Do
¡@If xF = "" Then xF = Dir(xPath & "*.txt") Else xF = Dir
¡@If xF = "" Then Exit Do
¡@If Not xS.[A:A].Find(xF, LookAT:=xlWhole) Is Nothing Then GoTo 101
¡@Set xEnd = xS.Cells(Rows.Count, 1).End(xlUp)(2)
¡@If xEnd.Row < 3 Then Set xEnd = xS.[A3]
¡@xEnd = xF
¡@
¡@Open xPath & xF For Input Access Read As #1
¡@Do Until EOF(1)
¡@¡@Line Input #1, T
¡@¡@N = Switch(T = "&", "B", T = "%", "M", T = "$", "AC", T = T, "")
¡@¡@'¡ÄT="&"¡A¨ú¢ÐÄæ¡AÃþ±À¡ã¡ã¡F§ä¤£¨ì"&%$"¡AN¬°ªÅ­È¡@
¡@¡@If N <> "" Then Set xR = xEnd(1, N): ¡@C = 0
¡@¡@'¡Ä§ä¨ì"&%$"«á¡A¥HxR©w¦ì¬°¦U¤ÀÃþªº­º®æ
¡@¡@For Each TT In Split(T, " ")
¡@¡@¡@¡@C = C + 1: ¡@xR(1, C) = TT
¡@¡@Next
¡@Loop
¡@Close #1
101: Loop
End Sub
¡@
¡@
¤j­P¦p¤W¡A¨ä¥L²Ó¸`½Ð¦Û¦æ§ó§ï©Î½Õ¾ã¡ã¡ã

­ã´£³¡ªL

N = Switch(T = "&", "B", T = "%", "M", T = "$", "AC", T = T, "")
¤]¥i¥Î¡G
N = Array("", "", "B", "M", "AC")(InStr("_&%$", T))
¡ÄInStr ­È¥u¦³0,1,2,3,4 ¤­ºØµ²ªG¡@

"_&%$"¡@«e­±¥["_"¡A¬O¬°¨¾¤îT¬OªÅ®æ®Éªº»~§P¡]»PFIND¨ç¼Æ¤@¼Ë¡Aµ²ªG¬°¢°¡^
¥i´ú¸Õ¡@MsgBox InStr("&%$", "")

Array «e­±¨â­ÓªÅ¦r²Å¡A§Y¬O¦bT¬°ªÅ­È¡]InStr­È¬°¢°¡^©Î§ä¤£¨ì¤å¦r®É¡]InStr­È¬°¢¯¡^¡A¥HªÅ¦r²ÅÅã¥Ü

­ã´£³¡ªL

¦^´_ 4# Jason80Lo

If Not xS.[A:A].Find(xF, LookAT:=xlWhole) Is Nothing Then GoTo 101
¡Ä¦pªG¤å¦rÀɦWºÙ¤w¦s¦b¡A²¤¹L¡@

Set xEnd = xS.Cells(Rows.Count, 1).End(xlUp)(2)
¡Ä¨ú±o·Ç³Æ¶ñ¤J¸ê®Æªº¦ì¸m¡]³Ì«á¤@µ§¸ê®Æªº¤U¤@®æªÅ¥Õ®æ¡^¡@

If xEnd.Row < 3 Then Set xEnd = xS.[A3]
¡Ä¦pªG³oªÅ¥Õ®æ¦C¸¹¤p¤_¢²¡A«h¨ú¢Ï¢²¡]¨¾¤î¼ÐÃDµL¤å¦rªº¿ù»~¡^¡@

xEnd = xF
¡Ä²Ä¤@®æ¶ñ¤å¦rÀɦW¡@

For Each TT In Split(T, " ")
¡@¡@C = C + 1: ¡@xR(1, C) = TT
Next
¡Ä¥HªÅ¥Õ®æ­åªR¤å¦r¡A¦A¦V¥k³v¤@¶ñ¤J¡@

­ã´£³¡ªL

¦^´_ 7# Jason80Lo


Do Until EOF(1)
¡@¡@Line Input #1, T
¡@¡@If T = "&" Then Set xR = xEnd(1, "B"): C = 0
¡@¡@If (T = "#" Or T = "$") And N = "" Then Set xR = xEnd(1, "M"): C = 0: N = "Y"
¡@¡@If T = "@" Then Set xR = xEnd(1, "AO"): C = 0
¡@¡@For Each TT In Split(T, " ")
¡@¡@¡@¡@C = C + 1:   xR(1, C) = TT
¡@¡@Next
Loop

­ã´£³¡ªL

¦^´_ 10# Jason80Lo


If (T = "#" Or T = "$") And N = "" Then Set xR = xEnd(1, "M"): C = 0: N = "Y"
¡Ä·í¹J"#"©Î"$"¡A§Y¥H³Ì«á¦Cªº¢ÛÄ欰¶ñ¤J¸ê®Æªº°_©l®æ¡A
¡@¦]"#"»P"$"­Ó¼Æ¤£¤@©w¡A¥B®É¦³®ÉµL¡A¤S¦ì¸m¨S¦³¬J©w¶¶§Ç¡A
¡@©Ò¥H¡A¥u­n¹J¨ì²Ä¤@­Ó"#"©Î"$"¡A§Y¥HN="Y"ªí¥Ü¤w¨ú±o°_©l®æ¡A¨ä«áªº´Nºâ¬O²Ö­p­Ó¼Æ¡ã¡ã
¡@
¸ê®Æ­n¦³©T©w³W«h¡]¨Ì´£¨Ñªº¬O¤T¬q¦¡¡^¡A§_«hµ{¦¡µLªk¼gªº¡I

­ã´£³¡ªL

¦^´_ 12# Jason80Lo


N = ""¡@'¦b³o¸Ì¥[¤J
Open xPath & xF For Input Access Read As #1


¦]²Ä¤@­Ó¤å¦rÀÉ°õ¦æ«á N = "Y"¡A
©Ò¥H¨C¦¸³£­n±N N ³]¦^ªÅ­È¡A¤~¤£·|³Q¿ù»~¤Þ¥Î¡I

­ã´£³¡ªL

¦^´_ 15# Jason80Lo


Sub TEST()
Dim xPath$, xF$, xS As Worksheet, xEnd As Range, xR As Range, TT, T, N$, C%
xPath = ThisWorkbook.Path & "\" '"C:\Users\j\Desktop\VBA TXT ¨Ì·Ó¬q¸¨¶}±Ò»Ý¨D\"
Set xS = ThisWorkbook.Sheets("¤u§@ªí1")
Do
¡@If xF = "" Then xF = Dir(xPath & "\*.txt") Else xF = Dir
¡@If xF = "" Then Exit Do
¡@If Not xS.[A:A].Find(xF, LookAT:=xlWhole) Is Nothing Then GoTo 101
¡@Set xEnd = xS.Cells(Rows.Count, 1).End(xlUp)(2)
¡@If xEnd.Row < 3 Then Set xEnd = xS.[A3]
¡@xEnd = xF
¡@
¡@N = ""¡@
¡@Open xPath & xF For Input Access Read As #1
¡@Do Until EOF(1)
¡@¡@¡@Line Input #1, T
¡@¡@¡@If T = "&" Then Set xR = xEnd(1, "B"): C = 0
¡@¡@¡@If T = "$" Then Set xR = xEnd(1, "BG"): C = 0
¡@¡@¡@If (T = "#" Or T = "%") And N = "" Then Set xR = xEnd(1, "FZ"): C = 0: N = "Y"
¡@¡@¡@If T = "@" Then Set xR = xEnd(1, "DX"): C = 0
¡@¡@¡@For Each TT In Split(T, " ")
¡@¡@¡@¡@¡@C = C + 1:   xR(1, C) = TT
¡@¡@¡@Next
¡@Loop
¡@Close #1
101: Loop
End Sub