ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Âà´«¤å¦r§Î¦¡·j´M

¦^´_ 30# wayne0303

¦]¬°±z³o­Ó¥N½X¬O³]©w³sµ²¥~³¡ªí®æ·j´M¡A¦pªG§Ú­n¥ý¥Î¥»¨­ÀÉ®×´ú¸Õ»P¤F¸Ñ¤§«á¦A®M¥Î¥~³¡ªºªí®æ...¨º¸Ó§ï­þÃä©O¡H
>> #20 ´N¦³»¡©ú¹L¤F¡A¤U­±¨º3­Ó²¾°£§Y¥i
'FPath = ThisWorkbook.Path
'Set WB = Workbooks.Open(FPath & "\²Î­pªí.xlsx")
...
...
'WB.Close

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-9-10 14:46 ½s¿è

¦^´_ 30# wayne0303


R = Range("f65536").End(3).Row >>³o¬Oªí®æ°_©l¦ì¸m¡H©Ò¥H±z³o­Ó¥N½X¨S¦³³]©wªí®æ·j´M½d³ò¤F¡H
Arr = Range("a1:a" & [a65536].End(3).Row)>>³o¬O¨Ó·½°t¹ïÀɦì¸m¡H   
>> #29 µ{¦¡½X¡A¥u¦³°w¹ï§A´£¨ì"§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥[" °µ­×§ï¦p¤U¡AÁÙ¦³¤ñ¹ï®É¤@¤p³¡¤À·s¼W¡A¤w¦³¼gµù¸Ñ¤F¡A½Ð¦Û¦æ¬ã¨s¤@¤U¡AÁÂÁÂ

Application.ScreenUpdating = False: Application.DisplayAlerts = False
'FPath = ThisWorkbook.Path  '¥»Àɮתº¸ô®|
'Set WB = Workbooks.Open(FPath & "\²Î­pªí.xlsx") '¶}±Ò¥»Àɮ׸ô®|¤Uªº²Î­pªí
'''°w¹ï"§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥["ªº³B²z¡A

With Sheets(1)
    If .FilterMode Then .ShowAllData
    R = Range("f65536").End(3).Row  'FÄæ³Ì«á¤@¦C
    C = Rows("3:" & R).Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column '§ä¨ì³Ì«á¤@Äæ
    Arr = Range([f1], Cells(R, C)) '¸ê®Æ¸Ë¤J°}¦C
    For i = 1 To UBound(Arr)
        If InStr(Arr(i, 1), "¥kÁ³") Then
            For j = 1 To UBound(Arr, 2)
                T = Arr(i + 2, j): If T = "" Then GoTo 95
                xD(T & "_1") = Arr(i + 1, j)   '¥kÁ³±Û¸ê®Æ¸Ë¨ì¦r¨å
95:         Next
        ElseIf InStr(Arr(i, 1), "¥ªÁ³") Then
            For j = 1 To UBound(Arr, 2)
                T = Arr(i + 2, j): If T = "" Then GoTo 96
                xD(T & "_2") = Arr(i + 1, j)    '¥ªÁ³±Û¸ê®Æ¸Ë¨ì¦r¨å
96:         Next
        ElseIf InStr(Arr(i, 1), "¥­") Then
            For j = 1 To UBound(Arr, 2)
                T = Arr(i + 2, j): If T = "" Then GoTo 97
                xD(T & "_3") = Arr(i + 1, j)    '¥­Ås¸ê®Æ¸Ë¨ì¦r¨å
97:         Next
        End If
    Next
End With
'WB.Close   'Ãö³¬²Î­pªí

'¤U­±ªº¤W¦¸ªº¤@¼Ë¡A¥u¦³·s¼W¦p¤U

Arr = Range("a1:a" & [a65536].End(3).Row) '­n³Q¤ñ¹ïªº¸ê®Æ
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "" Then GoTo 98 '·s¼W:·íAÄ檺Àx¦s®æµL¸ê®Æ¤£°µ°Ê§@
    If Left(Arr(i, 1), 1) = "L" Or Left(Arr(i, 1), 1) = "R" Then 'AÄæÀx¦s®æªº²Ä1­Ó¦r"L" "R"¤~°Ê§@

        w1 = Left(Arr(i, 1), 1): w2 = Mid(Arr(i, 1), 2, 1)
        If Asc(w1) > 64 And Asc(w1) < 123 And Asc(w2) > 64 And Asc(w2) < 123 Then
....
...

TOP

¯Â¤å¦r³B²z~~~

Sub TEST_A1()
Dim xU As Range, Arr, A, V, xR As Range, xD, T$
Set xD = CreateObject("Scripting.Dictionary")
Set xU = [¤u§@ªí1!d:w] '­Y¬O¸óÀÉ, ¥²¶·¥ý¥´¶}¸ÓÀÉ®×, ¦A«ü©w¤u§@ªí¤Î½d³ò
For Each A In Array("¥­Ås", "¥kÁ³±Û", "¥ªÁ³±Û")     
      For Each xR In xU.Find(A, Lookat:=xlWhole).MergeArea   'ª`·N:³o¬O¥H"¦X¨Ö®æ"§ì½d³ò
           xD(V & xR(3)) = xR(2)
    Next
    V = V + 1
Next
'---------------------------
Arr = Range([a1], [a65536].End(3))
For i = 5 To UBound(Arr)
    T = Replace(Replace(Arr(i, 1), "¢X", ""), "¥õ¨¤", "/")
    T = Split(Replace(Replace(T, "RR", "1R"), "LR", "2R") & "Âà", "Âà")(0)
    Arr(i - 4, 1) = xD(T)
Next i
[b5].Resize(UBound(Arr) - 4) = Arr
End Sub


================================

TOP

¦^´_ 32# samwang


·PÁÂsamwang¤j!
§Ú¦A¸Õ¬Ý¬Ý~

TOP

¦^´_ 33# ­ã´£³¡ªL


For Each xR In xU.Find(A, Lookat:=xlWhole).MergeArea   'ª`·N:³o¬O¥H"¦X¨Ö®æ"§ì½d³ò>>¥i¬O­ã¤j³o¼ËÁÙ¥²¶·½Æ»s¨ì¨ä¥¦¦a¤è¦X¨Ö¡A¤ñ¸û§Æ±æ¯à¥Î­ìªí®æ«¬¦¡·j´M...

TOP

¦^´_ 33# ­ã´£³¡ªL


­ã¤j§Ú¸Õ¤F±zªº¥N½X¥u·j¥X³Ì«á¤@­ÓR390ªº«~¸¹¡A¦A³Â·Ð±z¦³ªÅ¬Ý¤@¤U¬O§Ú½d³ò³]¿ù¤F¶Ü¡H
µM«á½Ð©¿²¤35#ªºµo¨¥~


ÁÂÁÂ~

Âà´«¤å¦r§Î¦¡·j´M_¨Ò.rar (22.69 KB)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2021-9-13 18:59 ½s¿è

¦^´_ 36# wayne0303

Sub TEST_A1()
Dim xU As Range, Arr, A, V, xR As Range, xD, T$
Set xD = CreateObject("Scripting.Dictionary")
Set xU = [¤u§@ªí1!f2:ad73]
For Each A In Array("¥­Ås", "¥kÁ³±Û", "¥ªÁ³±Û")
    For Each xR In xU.Find(A, Lookat:=xlWhole).Resize(1, 100)  '§ä¨ìÃöÁä¦r, ¦V¥kÂX®i100Äæ, ­Y¤£°÷¥Î¦Û§ï¤U(¦¹®É´N¤£¥ÎºÞ¦X¨Ö®æ¤F)  
        If xR(3) <> "" Then xD(V & xR(3)) = xR(2)
    Next
    V = V + 1
Next
'---------------------------
Arr = Range([a1], [a65536].End(3))
For i = 1 To UBound(Arr)  'AÄæ¸ê®Æ¥Ñ²Ä¤@¦æ¶}©l, ­n§ï¦¨ FOR I=1 TO ??   
    T = Replace(Replace(Arr(i, 1), "¢X", ""), "¥õ¨¤", "/")
    T = Split(Replace(Replace(T, "RR", "1R"), "LR", "2R") & "Âà", "Âà")(0)
    Arr(i, 1) = xD(T) '¦P¦æ¼g¤J, ³o´N¤£¶·¦A¥Î i-4   
Next i
[b1].Resize(UBound(Arr)) = Arr  'µ²ªG¸ê®Æ¸m¤J, ¶·¦P¨B±qB1¤U¤â   
End Sub


==========================================

TOP

¥»©«³Ì«á¥Ñ wayne0303 ©ó 2021-9-14 01:06 ½s¿è

¦^´_ 37# ­ã´£³¡ªL


³ø§i­ã¤j~¤º³¡ªí®æ¥i¥H¡A¦ý¡K¡K

Set xU = [¤u§@ªí1!d:w] '­Y¬O¸óÀÉ, ¥²¶·¥ý¥´¶}¸ÓÀÉ®×, ¦A«ü©w¤u§@ªí¤Î½d³ò >>¸óÀɳo­Ó·Ó±z»¡ªº¥´¶}¸ÓÀÉ¡A¤]«ü©w¤u§@ªí¦WºÙ¸ò½d³ò´ú¸Õ·|¥X²{'424吔...
2021-09-14_005855.jpg

TOP

¦^´_ 38# wayne0303

°Ñ¦ÒÀÉ:
TEST001.rar (27.31 KB)

TOP

¥»©«³Ì«á¥Ñ wayne0303 ©ó 2021-9-14 13:21 ½s¿è

¦^´_ 39# ­ã´£³¡ªL


­ã¤j¡A§Ú·Ó§ï°¨¶ë§J³¡¤À¡A´NÅܦ¨³o¼Ë¤F...
(100¬O°÷¥Îªº)
2021-09-14_125940_New.jpg

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD