PJChen

¦^´_ 30# ­ã´£³¡ªL
ÁÂÁ­ã¤j,
³o­Óµ{¦¡¤]¥i¥H«Ü·Ç½Tªº°õ¦æ¤F^^

¤@¼Ë¥H¤W¤äµ{¦¡¬°¼Òª©,¥H¤U3¤äµ{¦¡ªº¨Ï¥Î®É¾÷¤£¦P,»Ý­n³æ¿Wµ{¦¡¨Ï¥Î
1        ¥HKÄæ«~¦W¦C¼Æ¬°·Ç,ClearContents(¥u²M°£¸ê®Æ,¤£²M°£®æ¦¡) ­qÁʼƪº¼Æ­È
        ­n¯à®M¥Î¨ì³o­ÓBF²z³f¤u§@ªíªº¨C­Ó­qÁʼÆÄæ¦ì
        ¨ä¥LÀx¦s®æ¤£­n²M°£
       
2        ¥HKÄæ«~¦W¦C¼Æ¬°·Ç
        ¤é´Á¥H«~¦WªíÀYªº¤W¤@¦CBÄ欰·Ç
        ­n¯à®M¥Î¨ì³o­ÓBF²z³f¤u§@ªíªº¨C­ÓH,JÄæ¦ì
        HÄæ H3=$B1+T3-(V3+U3)-1
        JÄæ J3=$B1+T3-1
       
3        ¥HKÄæ«~¦W¦C¼Æ¬°·Ç,ClearContents H,JÄæ¦ì(¥u²M°£¸ê®Æ,¤£²M°£®æ¦¡)
        ­n¯à®M¥Î¨ì³o­ÓBF²z³f¤u§@ªíªº¨C­ÓH,JÄæ¦ì
        ¨ä¥LÀx¦s®æ¤£­n²M°£  
²z³f³æ.rar (145.21 KB)

¤U¸ü¦¸¼Æ: 3

2020-2-18 13:42

­ã´£³¡ªL

¦^´_ 31# PJChen


Sub ­qÁʼÆ_²M°£()
Dim R&, xR As Range, xH As Range, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       Range(xH(1, 7), xR(0, 7)).ClearContents
       C = 0
    End If
101: Next
End Sub

Sub ¤¹¦¬¤é_¤½¦¡()
Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Fx(1) = "=IF(J3="""","""",J3-U3)"
Fx(2) = "=IF(J3="""","""",""~"")"
Fx(3) = "=IF(N(B$_X)*LEN(K3)*N(T3)*N(U3)=0,"""",B$_X+T3-1)"
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       For j = 1 To 3
           Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, xH.Row), "_X", xH.Row - 2)
       Next j
       C = 0
    End If
101: Next
End Sub

Sub ¤¹¦¬¤é_²M°£()
Dim R&, xR As Range, xH As Range, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       Range(xH, xR(0, 0)).ClearContents
       C = 0
    End If
101: Next
End Sub


'==================================

PJChen

¦^´_ 32# ­ã´£³¡ªL
­ã¤j,
´ú¸Õ¦^³ø~~§Ú¬O¥Î¦P¤@­ÓÀÉ´ú¸Õªº,¦A³Â·Ð±zÀ°¦£­×§ï¤@¤U~~·PÁÂ
1) ¤½¦¡º|±¼VÄ檺¤¹¦¬¡ÓX....³o­Ó·|¦b¯S®í®É¶¡¥Î¨ì,¨Ï¥Î¨ì®É,·|Key¤J¼Æ­È,ªíÀY¥u¥´¦bV2Äæ,¦ý¨C­ÓHÄ檺¤½¦¡³£­n¥[¤JH3=$B$1+T3-(V3+U3)-1
2) §Ú·Q§â¤¹¦¬ªºH,JÄæ¦ì,key¤J¸ê®Æ«á¤]Åܦ¨­È(µL¤½¦¡),³o¼Ë¥i¥H¨ÏÀÉ®×run§Ö¨Ç
3) Sub ¤¹¦¬¤é_¤½¦¡()....´ú¸ÕÀɪº³Ì¥½2­Óªí(ªíÀY¼g"¥_")µLªk¶ñ¤J¤¹¦¬¤é_¤½¦¡,¦Ó¥B­ì¨ÓH,JÄæ¦ì¤¤¶¡ªº~,ÁÙ·|³Q²M°£
½Ð¥Î§Úªþ¤Wªº³o­ÓÀÉ,­ì¥ýªºÀÉ,§Ñ¤F¦b³Ì¥½2­Óªí¥´¤W¤é´Á
²z³f³æII.rar (146.03 KB)

¤U¸ü¦¸¼Æ: 3

2020-2-18 21:16

­ã´£³¡ªL

¦^´_ 33# PJChen

Sub ¤¹¦¬¤é_¤½¦¡()
Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Fx(1) = "=IF(J3="""","""",J3-U3-V3)"
Fx(2) = "=IF(J3="""","""",""~"")"
Fx(3) = "=IF(OR(B$_X="""",K3=""""),"""",B$_X+T3-1)"
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = xH.Row: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       For j = 1 To 3
           Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, C), "_X", C - 2)
       Next j
          With Range(xH, xR(0, 0)): .Value = .Value: End With
       C = 0
    End If
101: Next
End Sub


'==============================

PJChen

¦^´_ 34# ­ã´£³¡ªL
­ã¤j,
³o¨â¤Ñ¤@ª½¦b´ú¸Õ,µo²{¤¹¦¬¤é¤½¦¡±`±`·|¤À¤G¬q¦¡ªº¶ñ¤J¤é´Á,°õ¦æ²Ä¤@¦¸¥ý¶ñ¤JJÄæ¤é´Á,°õ¦æ²Ä¤G¦¸¦A¶ñ¤J~¤ÎHÄæ
¦X­pÄ榳®É¤]¤£¥[Á`
¥i¥HÀ°¦£¬d¤U¶Ü?
ªþ¥ó¬O§Ú¥Î¨Ó´ú¸Õªº2­Ó¤u§@ªí,¨ä¤¤¤@­Ó¤u§@ªíªº«~¦W§ï¬°"²£«~",µ{¦¡¦³¸òµÛ­×¥¿,2ºØ°õ¦æ¹Lµ{³£¬Û¦P
²z³f³æII.rar (234.04 KB)

¤U¸ü¦¸¼Æ: 9

2020-2-20 23:03

­ã´£³¡ªL

¦^´_ 35# PJChen

[¦Û°Ê­pºâ]³QÃö³¬¤F(«D¥²­n¤£¥i¦p¦¹³]©w, ¥i¯à·|µo¥Í¤@°ï°ÝÃD, ¨Ò¦p¤½¦¡µLªk§ó·s­pºâ..§ì¨ì¤@°ï¿ù»~¼Æ¾Ú..):
With Range(xH, xR(0, 0)):  .Value = .Value: End With
§ï¦¨:
With Range(xH, xR(0, 0)): .Calculate: .Value = .Value: End With

PJChen

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

­ã¤j¦n,
µ{¦¡§ï¬°With Range(xH, xR(0, 0)): .Calculate: .Value = .Value: End With ²{¦b¥¿±`¤F¡I¯uÁÂÁ§A..
§Ú¦b¤u§@®É,¥Î¨ì¤£¤Öµ{¦¡¤Î¤½¦¡,¦]¬°§@·~®É¶¡¬O¥H¬í­pªº,¤£±o¤£¥Î¤â°Ê­pºâ,©Ò¥H§Ú¤~·|¤@¨B¨B§ï¦¨µ{¦¡,§Æ±æ¥i¥HÁYµuµ¥«Ý®É¶¡,¤£µM¦³®É­Ôexcel·|·í±¼,¤£µM´N¬O®É¶¡¤Ó¤[,§Ú·|³Q±þ¤F....
§Ú¬Ý¨ìSub TEST_2,¦³­qÁʼƪº¥[Á`¥\¯à,½Ð°ÝSub TEST_1¥i¥H³o¼Ë°µ¶Ü?¦ý¬O¥[Á`«á§Ú·Q­È¤Æ,¤£­n¦³¤½¦¡...

1. Sub TEST_1()
   
2. Dim R&, Arr, Brr, i&, S&(1 To 2), V1, V2, C%
   
3. R = Cells(Rows.Count, "K").End(xlUp).Row
   
4. If R <= 2 Then Exit Sub
   
5. Arr = Range("K2:Q" & R)
   
6. Brr = Range("M2:N" & R)
   
7. For i = 1 To UBound(Arr)
   
8.     If Arr(i, 1) = "«~¦W" Then Erase S: C = 1: GoTo 101
   
9.     If Arr(i, 1) = "¦X­p" Then
   
10.        Brr(i, 1) = S(1) '½c¼Æ¦X­p
    
11.        Brr(i, 2) = S(2) '²~¼Æ¦X­p
    
12.        Erase S: C = 0: GoTo 101
    
13.     End If
    
14.     If C = 1 Then
    
15.        Brr(i, 1) = "":    Brr(i, 2) = ""
    
16.        V1 = Val(Arr(i, 6)) '¥]¸Ë¼Æ
    
17.        V2 = Val(Arr(i, 7)) '­qÁʼÆ
    
18.        If Arr(i, 2) = "" Or V1 = 0 Then GoTo 101
    
19.        Brr(i, 1) = Int(V2 / V1) '½c¼Æ
    
20.        S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
    
21.        Brr(i, 2) = V2 Mod V1  '²~¼Æ
    
22.        S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
    
23.     End If
    
24. 101: Next i
    
25. Range("M2:N" & R) = Brr
    
26. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

¦^´_ 37# PJChen

Sub TEST_1()
Dim R&, Arr, Brr, i&, S&(1 To 3), V1, V2, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Arr = Range("K2:Q" & R)
Brr = Range("M2:N" & R)
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "«~¦W" Then Erase S: C = 1: GoTo 101
    If Arr(i, 1) = "¦X­p" Then
       Brr(i, 1) = S(1) '½c¼Æ¦X­p
       Brr(i, 2) = S(2) '²~¼Æ¦X­p
       Cells(i + 1, "Q") = S(3) '­qÁʼƦX­p
       Erase S: C = 0: GoTo 101
    End If
    If C = 1 Then
       Brr(i, 1) = "":    Brr(i, 2) = ""
       V1 = Val(Arr(i, 6)) '¥]¸Ë¼Æ
       V2 = Val(Arr(i, 7)) '­qÁʼÆ
       If Arr(i, 2) = "" Or V1 = 0 Then GoTo 101
       Brr(i, 1) = Int(V2 / V1) '½c¼Æ
       S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
       Brr(i, 2) = V2 Mod V1  '²~¼Æ
       S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
       S(3) = S(3) + V2 '­qÁʼƲ֭p
    End If
101: Next i
Range("M2:N" & R) = Brr
End Sub


'========================

PJChen

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

­ã¤j¦n,

§Ú·Q¼W¥[¤@­Ó³æ¿Wµ{¦¡,°µ¬°¯S®í­q³æ ¼W¥[or´î¤Ö ¥X³f¼Æ
½Ð±Ð­n¦p¦ó¨Ì¤§«eªºµ{¦¡¼Ò¦¡­×§ï??(¤@¼Ë¥H«~¦WÄ檺¸ê®Æ¬°¨Ì¾Ú)
1) RÄæ(¥[´î¼Æ¶q)¥[¤J¤½¦¡,¤§«á­È¤Æ
"=-SUMPRODUCT(([³Ì·s®w¦s.xlsx]¤ñµá¦h!$F$4:$F$70=$L3)*([³Ì·s®w¦s.xlsx]¤ñµá¦h!$CD$3:$CV$3=$B3)*([³Ì·s®w¦s.xlsx]¤ñµá¦h!$CD$4:$CV$70))"
2) QÄæ­qÁÊ¼Æ "=Q3+R3"¤§«á­È¤Æ
²z³f³æII.rar (204.97 KB)

¤U¸ü¦¸¼Æ: 3

2020-3-26 22:39

PJChen

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

­ã¤j¦n,

§Ú§â¤§«eªºµ{¦¡®³¨Ó­×§ï«á,³£µLªk°õ¦æ,¥i§_À°¦£¬Ý¤U??

1. Sub ¹º³æ_¤½¦¡()
   
2. Dim R&, Fx$(1 To 2), xH As Range, C%, j%
   
3. Application.ScreenUpdating = False
   
4. Application.DisplayAlerts = False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
   
5. Application.Calculation = xlManual     '¤â°Ê­pºâ
   
6. Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
   
7. 
   
8. R = Cells(Rows.Count, "K").End(xlUp).Row
   
9. If R <= 2 Then Exit Sub
   
10. Fx(1) = "=-SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$70=$L3)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$3:$CV$3=$B3)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$4:$CV$70))"
    
11. Fx(2) = "=Q3+R3"
    
12. For Each xR In Range("K2:K" & R)
    
13.     If xR = "«~¦W" Then Set xH = xR(1, 8): C = xH.Row: GoTo 101
    
14.     If xR = "¦X­p" Then
    
15.        If C = 0 Then GoTo 101
    
16.        For j = 1 To 2
    
17.        Next j
    
18.             With Range(xH, xR(0, 8)): .Calculate: .Value = .Value: End With
    
19.        C = 0
    
20.     End If
    
21. 101: Next
    
22. End Sub

½Æ»s¥N½X

¹º³æ.rar (328.67 KB)

¤U¸ü¦¸¼Æ: 3

2020-3-30 19:16