jcchiang

¦^´_ 50# PJChen
¸Õ¸Õ¬Ý!!
Sub ex()
Dim x%, i%
Dim xR As Object
For Each xR In Range([b5], [b65535].End(3))
   If xR = "­q³æ" Or xR = "¼t¯Ê" Or xR = "¹ê¥X¼Æ" Then
      For i = 1 To 8
         If xR.Offset(, i) <> 0 Then
            x = Application.WorksheetFunction.Quotient(xR.Offset(, i), [C3])  '­pºâ½c¼Æ
            xR.Offset(, i) = xR.Offset(, i) & "=" & vbCrLf & x & "½c+" & xR.Offset(, i) Mod [C3]
         End If
      Next
   End If
Next
End Sub

PJChen

¦^´_ 51# jcchiang

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

jcchiang

¦^´_ 52# PJChen

For Each xR In Range([b5], [b65535].End(3))
³o¬OÅýxR¦bRange([b5], [b65535].End(3))³o­Ó½d³ò¤º°õ¦æ

If xR = "­q³æ" Or xR = "¼t¯Ê" Or xR = "¹ê¥X¼Æ" Then
¤~¬O§PÂ_­n­pºâªº¦C

PJChen

¦^´_ 53# jcchiang

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

PJChen

¦^´_ 53# jcchiang

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

PJChen

¦^´_ 53# jcchiang

¤j¤j¦n,
²z³f³æII.xlsx¬°¨Ó·½ÀÉ,BF²z³f.sheet¥HBÄ檺«È¤á¬°¦C¼Æ
BÄ檺«È¤á¹ïÀ³"«È¤á.sheet"ÂkÃþ
ÂkÃþ¦WºÙ¬Û¦Pªº,¨ä«È¤áªºªí®æ¥þ³£¬Û¦P
EX:
B252:B274¬°ªL¤f...¹ïÀ³ÂkÃþ¬°"¤¤¥x"
G:Pªº¸ê®Æ,B252:B274¦³23¦C
½Æ»sG252:P274ªº¸ê®Æ
¥´¶} "¤½¥Î²z³f-¤¤¥x.xlsx" (ÂkÃþªºÀɦW¦³§t"¤¤¥x")
¥u¶K­È¨ì"2.sheet" C3 (Âл\"¤½¥Î²z³f-¤¤¥x.xlsx"ªº¸ê®Æ)
¥H¤W¬O»Ý­n°õ¦æªº°Ê§@

¤§«e§Ú³£¬O¥ÎÃþ¦ü³o¼Ëªº»yªk
            xRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
                Range("C3:L" & xRow) = "=¤½¦¡"
                Range("C3:L" & xRow).Value = Range("C3:L" & xRow).Value
1) ²{¦b­n¥H7­ÓÂkÃþ¦WºÙ¥h§ä½Æ»sªº¬¡°Ê½d³ò
²ÎÎëA
²ÎÎëB
¤¤¥x
OK
µÜ
¥Ã±d
±o¦Ü

2) ²z³f³æII.xlsx BÄ檺«È¤á,­pºâ­Ó¼Æ§@¬° ½Æ»sªº¦C¼Æ¨Ì¾Ú
½Ð±Ð»yªk­n«ç»ò¼g?
¸ê®Æ.rar (313.11 KB)

¤U¸ü¦¸¼Æ: 4

2020-5-16 22:01

PJChen

¦^´_ 53# jcchiang
±z¦n,
©ÎªÌ¥ý±q¤ñ¸û²³æªº¶}©l
EX:
B252:B274¬°ªL¤f

­n¦p¦ó«ü©w½Æ»s¦C¼Æ,¤]´N¬O¦Û°Ê­pºâ B:B="ªL¤f"ªº¦C¼Æ=23¦C
µM«á¥i¥H«ü©w°Ï¶¡G252:P274¦¨¬°½Æ»sªº¨Ó·½?

jcchiang

¦^´_ 57# PJChen
³Ìªñ¤ñ¸û¦£,¨S®É¶¡¬ã¨s

Sub ex()
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
For Each a In Range("B:B")
    If a = "ªL¤f" Then d(a.Address) = d(a.Address)  '§ìªL¤fªº¦ì¸m,¼t°Ó¦Û¦æ§ó´«
Next
Range(d.Keys()(0)).Offset(, 5).Resize(d.Count, 10).Select '¿ï¾Ü½d³ò,½Ð¦Û¦æ¥[¤J½Æ»s¨ì­þªºµ{¦¡
End Sub

PJChen

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

­ã¤j¦n,
²z³fªí®æ¥Ø«e»Ý­n¤@­Ó±Æ§Ç¥\¯à,¥Ø«e¥H¤§«eµ{¦¡­×§ï,¥i¥H±Æ§Ç,¦ýµLªk¨Ì·Ó§Ú·Q­nªº¥\¯à±Æ,
¥i§_À°¦£¬Ý¤U¦p¦ó¨Ì¥H¤U»Ý¨D§@±Æ§Ç?   ²z³f³æ.rar (427.55 KB)

¤U¸ü¦¸¼Æ: 3

2020-5-22 20:51

Åý¨C­Ó«È¤áªí®æ,¥u­nFÄ榳¼Æ¦rªº,´N¨Ìkey1-FÄæ,key-2-LÄæ±Æ§Ç
¼Æ¦r¥Ñ¤p¨ì¤j,ªí®æFÄæªÅ¥ÕªÌ,´N²¤¹L¤£±Æ§Ç(¦]¦³¨Ç«È¤á¤£»Ý­n¦¹¥\¯à)

±Æ§Ç«á,ÁÙ·|¥Î¨ìÂk¦ìªº¥\¯à,¥t¤@µ{¦¡´N¥HSÄ檺¼Æ¦r°µÂk¦ì°Ê§@,¼Æ¦r¥Ñ¤p¨ì¤j

1. Sub ²z³f±Æ§Ç()
   
2. Dim R&, xR As Range, xH As Range, C%
   
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(2): C = 1: GoTo 101
   
7.     If xR = "¦X­p" Then
   
8.        If C = 0 Then GoTo 101
   
9.        Set S = Range(xH(1, -4), xR(0, -4))
   
10.        C = 0
    
11.         .Cells.Sort Key1:=S, Key2:=.Columns("L"), Header:=xlNo    'SortingµLªíÀY
    
12.     End If
    
13. 101: Next
    
14. End With
    
15. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

¦^´_ 59# PJChen

Sub ²z³f±Æ§Ç()
Dim R&, xR As Range, xH As Range, C%, V&
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: V = 0: GoTo 101
    V = V + Val(xR(1, -4))
    If xR = "¦X­p" Then
       If C = 0 Or V = 0 Then GoTo 101
       With Range(xH(1, -4), xR(0, 7))
           .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, _
                 Key2:=.Cells(1, 7), Order2:=xlAscending, Header:=xlNo
       End With
       C = 0: V = 0
    End If
101: Next
End Sub