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

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

¦^´_ 20# samwang




«D±`·PÁÂsamwang¤j¤j ¤£¹½¨ä·ÐªºÀ°¦£¡I

TOP

¥»©«³Ì«á¥Ñ wayne0303 ©ó 2021-9-9 16:56 ½s¿è

¦^´_ 20# samwang

samwang¤j ¤S¦³°ÝÃD¤F~
§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥[
§Ú­×§ï¤F¥N½X(¬õ¦r)½d³ò³¡¤À¦ý¦n¹³¨S§@¥Î...
À³¸Ó­n¦A§ï­þÃä©O¡H

    Sub §ó·s()
Dim Arr, xD, T1$, T2$, T$, w1$, w2$, i&, j&, k%
Set xD = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set WB = Workbooks.Open("D:\¸ê®Æ§¨123\¬¡­¶Ã¯abc.xlsx")
Arr = Range("d3:w16")
WB.Close

For i = 2 To UBound(Arr) Step 6
    k = k + 1
    For j = 1 To UBound(Arr, 2)
        T = Arr(i, j): If T = "" Then GoTo 99
        xD(T & "_" & k) = Arr(i - 1, j)
    Next j
99: Next i
....
...
....

ÁÙ¦³³sµ²¥~³¡ªí®æªº®É­Ô·|¥X²{°õ¦æ¶¥¬q¿ù»~'1004':

ÁÂÁÂ

2021-09-09_165136_New.jpg (20.28 KB)

2021-09-09_165136_New.jpg

TOP

¦^´_ 22# wayne0303

§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥[ >> ¥i¥H´£¨Ñ¹ê»Ú®×¨Ò½d¨Ò¡A¥B»¡©ú¦ó¿×¤£¹ïµ¥¼W¥[? ÁÂÁÂ
   
ÁÙ¦³³sµ²¥~³¡ªí®æªº®É­Ô·|¥X²{°õ¦æ¶¥¬q¿ù»~'1004':  >> ½Ð´£¨Ñ½d¨Ò¡AÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ wayne0303 ©ó 2021-9-9 19:55 ½s¿è

¦^´_ 23# samwang

¦^´_  wayne0303

§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥[ >> ¥i¥H´£¨Ñ¹ê»Ú®×¨Ò½d¨Ò¡A¥B»¡©ú¦ó¿×¤£¹ïµ¥¼W¥[? ÁÂÁÂ
,                                     ¡ô¡ô¡ô¤£¦n·N«ä§Ú¬O«ü¤u§@¤W·|»Ý­n¼W¥[
...
samwang µoªí©ó 2021-9-9 17:31


ÁÙ¦³³sµ²¥~³¡ªí®æªº®É­Ô·|¥X²{°õ¦æ¶¥¬q¿ù»~'1004':  >> ½Ð´£¨Ñ½d¨Ò¡AÁÂÁÂ
    §Ú´N¬O§â³oÃ䪺¸ô®|§ï¦¨¤½¥qªº°Ïºô¸ÌªºÀɮ׸ô®|¦Ó¤wSet WB = Workbooks.Open("¤½¥q¸ô®|")¡A³o­Ó§Ú¤]¤£ª¾¹D«ç»ò´£¨Ñ½d¨Ò...¤£¯à¸Ñ´N³o¼Ë§a

ÁÂÁÂsamwang¤j~

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

TOP

¦^´_ 24# wayne0303

§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥[
>>½Ð°Ý±zªº¸ê®Æ¬O±qKÄæ¶}©l¶Ü? 3­Ó¸ê®Æªº¤¤¶¡¤£·|¦³¨ä¥¦ªº¸ê®Æ¹ï§a?

§Ú´N¬O§â³oÃ䪺¸ô®|§ï¦¨¤½¥qªº°Ïºô¸ÌªºÀɮ׸ô®|¦Ó¤wSet WB = Workbooks.Open("¤½¥q¸ô®|")
>>¦p±z¼gªºSet WB = Workbooks.Open("¤½¥q¸ô®|")¡A¤½¥q¸ô®|´N´«¦¨¹ê»Úªº¸ô®|§Y¥i

TOP

¦^´_ 25# samwang

§ÚªºÄæ¦C³£·|(¤£¹ïµ¥ªº)¼W¥[
>>½Ð°Ý±zªº¸ê®Æ¬O±qKÄæ¶}©l¶Ü? 3­Ó¸ê®Æªº¤¤¶¡¤£·|¦³¨ä¥¦ªº¸ê®Æ¹ï§a?>>¦pªþ¥ó¡A¹ê»Ú¬O±qDÄæ¶}©l

§Ú´N¬O§â³oÃ䪺¸ô®|§ï¦¨¤½¥qªº°Ïºô¸ÌªºÀɮ׸ô®|¦Ó¤wSet WB = Workbooks.Open("¤½¥q¸ô®|")
>>¦p±z¼gªºSet WB = Workbooks.Open("¤½¥q¸ô®|")¡A¤½¥q¸ô®|´N´«¦¨¹ê»Úªº¸ô®|§Y¥i>>¤½¥q¸ô®|´N¬OÀɮשñ¸mªº¦ì¸m

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

TOP

¦^´_ 26# wayne0303

§Ú´N¬O§â³oÃ䪺¸ô®|§ï¦¨¤½¥qªº°Ïºô¸ÌªºÀɮ׸ô®|¦Ó¤wSet WB = Workbooks.Open("¤½¥q¸ô®|")
>>¦p±z¼gªºSet WB = Workbooks.Open("¤½¥q¸ô®|")¡A¤½¥q¸ô®|´N´«¦¨¹ê»Úªº¸ô®|§Y¥i>>¤½¥q¸ô®|´N¬OÀɮשñ¸mªº¦ì¸m
>> ÀɦW¬O......\¬¡­¶Ã¯abc.xlsx¡A¹ï¶Ü?

TOP

¦^´_ 27# samwang


  §Ú´N¬O§â³oÃ䪺¸ô®|§ï¦¨¤½¥qªº°Ïºô¸ÌªºÀɮ׸ô®|¦Ó¤wSet WB = Workbooks.Open("¤½¥q¸ô®|")
>>¦p±z¼gªºSet WB = Workbooks.Open("¤½¥q¸ô®|")¡A¤½¥q¸ô®|´N´«¦¨¹ê»Úªº¸ô®|§Y¥i>>¤½¥q¸ô®|´N¬OÀɮשñ¸mªº¦ì¸m
>> ÀɦW¬O......\¬¡­¶Ã¯abc.xlsx¡A¹ï¶Ü?  >> ÀɦW¬O......\¡³¡³¡³²Î­pªí.xlsx ¡ö³o¼Ë

TOP

¦^´_ 28# wayne0303

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, xD, T1$, T2$, T$, w1$, w2$, i&, j&, k%, R%, C%
Set xD = CreateObject("Scripting.Dictionary")
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False: Application.DisplayAlerts = False
FPath = ThisWorkbook.Path
Set WB = Workbooks.Open(FPath & "\²Î­pªí.xlsx")
With Sheets(1)
    If .FilterMode Then .ShowAllData
    R = Range("f65536").End(3).Row
    C = Rows("3:" & R).Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Arr = Range([f1], Cells(R, 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)
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)
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)
97:         Next
        End If
    Next
End With
WB.Close
Arr = Range("a1:a" & [a65536].End(3).Row)
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "" Then GoTo 98
    If Left(Arr(i, 1), 1) = "L" Or Left(Arr(i, 1), 1) = "R" Then
        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
            If UCase(w1) = "L" Then
                T1 = Mid(Split(Arr(i, 1), "¥õ")(0), 2)
                T2 = Split(Split(Arr(i, 1), "¨¤")(1), "¢X")(0)
                T = T1 & "/" & T2 & "_" & 2: Arr(i, 1) = xD(T)
            End If
            If UCase(w1) = "R" Then
                T1 = Mid(Split(Arr(i, 1), "¥õ")(0), 2)
                T2 = Split(Split(Arr(i, 1), "¨¤")(1), "¢X")(0)
                T = T1 & "/" & T2 & "_" & 1: Arr(i, 1) = xD(T)
            End If
        Else
            T = Split(Arr(i, 1), "Âà")(0) & "_" & 3: Arr(i, 1) = xD(T)
        End If
    End If
98: Next
Range("b1").Resize(UBound(Arr)) = Arr
Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub

TOP

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

¦^´_ 29# samwang

³sµ²·|Åܦ¨...§ä¤£¨ì(¦pªþ¹Ï)
§âÀɮ׽ƻs¨ì®à­±¥i¥H°õ¦æ¦ý·j´Mµ²ªG¬OªÅ¥Õ...
©Ò¥H°Ý¤@¤U¤j¤j~
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

¦]¬°±z³o­Ó¥N½X¬O³]©w³sµ²¥~³¡ªí®æ·j´M¡A¦pªG§Ú­n¥ý¥Î¥»¨­ÀÉ®×´ú¸Õ»P¤F¸Ñ¤§«á¦A®M¥Î¥~³¡ªºªí®æ...¨º¸Ó§ï­þÃä©O¡H

ÁÂÁÂ

2021-09-10_133023_New.jpg (19.97 KB)

2021-09-10_133023_New.jpg

TOP

        ÀR«ä¦Û¦b : ¦a¤WºØ¤Fµæ¡A´N¤£©öªø¯ó¡F¤ß¤¤¦³µ½¡A´N¤£©ö¥Í´c¡C
ªð¦^¦Cªí ¤W¤@¥DÃD