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

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

PJChen

¦^´_ 38# ­ã´£³¡ªL
½Ð­ã¤j«üÂI, µ{¦¡¹B§@¤@ª½¤£¥¿±`....

1. Dim R&, xR As Range, xH As Range, C%
   
2. Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
   
3. R = Cells(Rows.Count, "K").End(xlUp).Row
   
4. If R <= 2 Then Exit Sub
   
5. For Each xR In Range("K2:K" & R)
   
6.     If xR = "«~¦W" Then Set xH = xR(1, 8): C = 1: GoTo 101
   
7.     If xR = "¦X­p" Then
   
8.        If C = 0 Then GoTo 101
   
9.        With Range(xH, xR(1, 8)) 'RÄæ¶ñ¤J¤½¦¡
   
10.             .Formula = "=-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.             .Value = .Value
    
12.             Range("R:R").Replace "0", "", 1  '*****(1,§¹¥þ²Å¦X)
    
13.        End With
    
14.        C = 0
    
15.     End If
    
16. 101: Next
    
17. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 42# ­ã´£³¡ªL
½Ð°Ý­ã¤j,
¥i§_¸Ñ»¡ ¥H¤U¬õ¦r

If Rw <= 2 Then Exit Sub
    If xR = "«~¦W" Then Set xH = xR(2, 8): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, 8))
.Replace 0, "", 1....³o¸Ì¤w¸g§â0¨ú¥N¬°ªÅ¥Õ,¬°¤°»òÁٻݭn[R2] = ""

PJChen

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

­ã¤j¦n,
§Ú±Nµ{¦¡¥uµy§@­×§ï,®M¥Î¨ìFÄæ,¦ý¨C¦¸°õ¦æµ{¦¡,F2³£·|³Q²M°£,¸Õ¤F¦h¦¸,¨ÌµM§ä¤£¨ì­ì¦],¤£©ú¥Õ¬°¤°»ò¦P¤@µ{¦¡·|¦³¤£¦Pµ²ªG?
µ{¦¡¦p¤U¡G

1. Sub ¼t¯Ê¸ü¤J()
   
2. Dim Rw&, xR As Range, xH As Range, C%, Fx$
   
3. Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
   
4. Rw = Cells(Rows.Count, "K").End(xlUp).Row
   
5. If Rw <= 2 Then Exit Sub
   
6. [F2] = "=IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=0,"""",IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=$Q2,""¼t¯Ê"",""¯Ê""&SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))))"
   
7. For Each xR In Range("K2:K" & Rw)
   
8.     If xR = "«~¦W" Then Set xH = xR(2, -4): C = 1: GoTo 101
   
9.     If xR = "¦X­p" Then
   
10.        If C = 0 Then GoTo 101
    
11.        With Range(xH, xR(0, -4)) 'FÄæ¶ñ¤J¤½¦¡
    
12.             .FormulaR1C1 = [F2].FormulaR1C1
    
13.             .Value = .Value
    
14.             .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
    
15.        End With
    
16.        C = 0
    
17.     End If
    
18. 101: Next
    
19. [F2] = ""
    
20. End Sub

½Æ»s¥N½X

¼t¯Ê¸ü¤J.rar (331.79 KB)

¤U¸ü¦¸¼Æ: 4

2020-4-6 23:57

PJChen

¦^´_ 12# jcchiang

±z¦n,
§Ú§â³o­Óµ{¦¡¼gªkÀ³¥Î¦b¥t¤@¬d±bªí®æ¤¤,¨Ã¥B
·Q¥[¤J¤@­Ó·s¥\¯à,Åý ¦C5:6 & ¦C8 &¦C11:12 & ¦C14 ªº¼Æ­È,¯à°÷¥[¤J½c²~ ,¦ý­ì¥ýªº¼Æ­È¤£­nÅÜ°Ê
½Ð°Ý³oºØ»yªk¸Ó«ç»ò¼g?   ¥H­ì­È_¥[¤J½c²~.rar (10.12 KB)

¤U¸ü¦¸¼Æ: 4

2020-5-10 11:47

EX1: C5ªº­ì­È¬°385
«hÅã¥Ü­È (=¤§«áªº¼Æ­È­n´«¦æ)
385=
19½c+5
²~ªº¦r¼Ë³£¤£Åã¥Ü,¦p²~¼Æ¬°0,«hÅV¥Ü­È¬°19½c+0

EX2: E5ªº­ì­È¬°19
«hÅã¥Ü­È  (=¤§«áªº¼Æ­È­n´«¦æ)
19=
0½c+19

µù¡G
ªí®æ¤ºªº¼Æ­È·|ÀHµÛ²£«~¤£¦P¦ÓÅÜ°Ê
­ìÀx¦s®æ ¦C5:6 & ¦C8 &¦C11:12 & ¦C14 ªº¼Æ­È,¸ü¤J«á³£¤w­È¤Æ
½c²~ªº­pºâ,¥H­ìÀx¦s®æ ¦C5:6 & ¦C8 &¦C11:12 & ¦C14 ªº¼Æ­È,¥h°£¥HC3ªº¤J¼Æ

PJChen

¦^´_ 51# jcchiang

±z¦n,
½Ð°Ý ³o­Ó¥Îªk,¬°¦ó¬O3¦C? End(3)
For Each xR In Range([b5], [b65535].End(3))

PJChen

¦^´_ 53# jcchiang

¦ý§Ú¤£©ú¥ÕEnd(3)ªº¥Îªk,3¦³¯S§O·N«ä¶Ü?

PJChen

¦^´_ 53# jcchiang

¯uÁÂÁ§A,§Úª¾¹D¥Îªk¤F