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

[µo°Ý] VBA­pºâ½c¤l¸¹½X

[µo°Ý] VBA­pºâ½c¤l¸¹½X

¥»©«³Ì«á¥Ñ john2006168 ©ó 2021-4-22 11:59 ½s¿è

½Ð°Ý¦³¨S¦³¦Ñ®v¥i¥HÀ°¦£¼g¤@¤U,¾Ç²ß¤@¤U¤£¦Pªº«ä¸ô?
·Q­n¶À¦âªºµ²ªG
revised plt4
PLT NO.        4        40        C1-C40        C

Â^¨ú.PNG (7.19 KB)

Â^¨ú.PNG

TEST VBA1.zip (11.02 KB)

¦^´_ 1# john2006168


¤£¦n·N«ä¡A¬Ý¤£¤ÓÀ´¸Ñ³W«h¬O¤°»ò¡A¥i§_½Ð¦A²Ó»¡©ú¤@¤U¡AÁÂÁÂ

TOP

¦^´_ 2# samwang
¦Ò¼{±ø¥óEÄæ¬O¶¶§ÇP©MCªº,¦pªG¬OPªO,²Ä¤@ªO¬O10¥ó,½s¯È½c¸¹½X´N¥ÎP1-P10,²Ä¤GªO20¥ó,´N¥ÎP11-P30,¥H¦¹Ãþ±À.,¦pªG¬OCªO,²Ä¤@ªO¬O10¥ó,½s¯È½c¸¹½X´N¥ÎC1-C10,²Ä¤GªO20¥ó,´N¥ÎC11-C30,¥H¦¹Ãþ±À.

TOP

¦^´_ 3# john2006168

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

Sub test()
Dim Arr, xD, TT, T, T1, T2, T3, N%, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = [A1].CurrentRegion
For i = 2 To UBound(Arr)
    T = Arr(i, 5): T2 = Arr(i, 2): T3 = Arr(i, 3)
    If xD.Exists(T & "") Then
        If N = 0 Then
            N = 1: TT = xD(T & "")(0)
        Else
            TT = xD(T & "")(0) + T1
        End If
        Arr(i, 1) = Arr(i, 5) & TT & "1" & "-" & T & T3 + xD(T & "")(1)
        xD(T & "") = Array(T2, T3 + xD(T & "")(1))
    Else
        Arr(i, 1) = Arr(i, 5) & "1" & "-" & T & T3
        xD(T & "") = Array(T2, T3): N = 0: T1 = xD(T & "")(0)
    End If
Next
Arr(1, 1) = "Carton"
Range("D1").Resize(UBound(Arr), 1) = Arr
End Sub

TOP

¦^´_ 4# samwang

ÁÂÁ¦Ѯv.³o­Ó¥i¥H,·PÁ©â®É¶¡¦^ÂÐ,¦ý¬O¬Ý±o¤£¬O«ÜÀ´.¦pªG¦³ª`¸Ñ·|§ó¦nÂI.
¥t¥~,½Ð°Ý¦³¨S¦³¨ä¥L¤j¤j,¥i¥H´£¨Ñ¨ä¥L¼gªk(¤£¥ÎUBound ¨ç¼Æ)

TOP

¦^´_ 5# john2006168

§ÚºÉ¤O¥[¤Jµù¸Ñ¡A¥i¯à¦³¨Ç¼g±o¤£¦n¤£¸Ô²Ó¡A½Ð±z¦Û¦æ¬ã¨s¤F¡AÁÂÁ¡C

Sub test1()
Dim Arr, xD, TT, T, T1, T2, T3, N%, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = [A1].CurrentRegion   '¸ê®Æ©ñ¤J¼Æ²Õ
For i = 2 To UBound(Arr)
    'T:EÄæ¸ê®Æ¡BT2:BÄæ¡BT3:CÄæ
    T = Arr(i, 5): T2 = Arr(i, 2): T3 = Arr(i, 3)
    If xD.Exists(T & "") Then '¦r¨å¦³µLEÄæ¸ê®Æ
        If N = 0 Then
            N = 1: TT = xD(T & "")(0) '­«½Æ¥X²{ªº²Ä1¦¸¨Ì³W«h½s¸¹(²Ä2½X)
        Else
            TT = xD(T & "")(0) + T1 '­«½Æ¥X²{ªº²Ä2¦¸¥H¤W¨Ì³W«h½s¸¹(²Ä2½X)
        End If
        Arr(i, 1) = Arr(i, 5) & TT & "1" & "-" & T & T3 + xD(T & "")(1) '¨Ì³W«h½s¸¹
        xD(T & "") = Array(T2, T3 + xD(T & "")(1)) 'CÄæ(kg)²Ö¥[¸Ë¤J¦r¨å
    Else
        Arr(i, 1) = Arr(i, 5) & "1" & "-" & T & T3  '²Ä1¦¸¥X²{¡A¨Ì³W«h½s¸¹©ñ¤JArr
        xD(T & "") = Array(T2, T3): N = 0: T1 = xD(T & "")(0) 'B,CÄæ¸ê®Æ©ñ¤J¦r¨å¡AT1³]¬°BÄæ²Ä¤@¦¸¥X²{¼Æ­È¡AN=0Âk¹s(P-C)
    End If
Next
Arr(1, 1) = "Carton"
Range("d1").Resize(UBound(Arr), 1) = Arr
End Sub

TOP

¦^´_ 6# samwang


   ¤£¦n·N«ä,¦pªG¥[¸ê®Æ¥h¨ì²Ä5¦æ,¼Æ¦r¤£¹ï.À³¸Ó¬OP61-P90

Â^¨ú.PNG (12.38 KB)

Â^¨ú.PNG

TOP

¦^´_ 7# john2006168


½Ð°ÝPXX - ªº³W«h¦p¦ó??

TOP

¦^´_ 8# samwang

¬O¥ó¼Æ,­n²Ö¥[.

TOP

¦^´_ 9# john2006168

§Ú¦A±Nexcelµ¹±z¬Ý¤@¤U

TEST VBA1 (2).zip (13.41 KB)

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤Hªº§Ö¼Ö¡D¤£¬O¦]¬°¥L¾Ö¦³±o¦h¡A¦Ó¬O¦]¬°¥L­p¸û±o¤Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD