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

[µo°Ý] ¦Û°Ê®Mªí

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

­ã¤j¦n,

¤£¦n·N«ä,³o­Ó·sªí®æ¬O³o´X¤Ñ¤~±Ò¥Î,µo²{®æ¦¡¨S°µ¦n,­n§ó§ï¦p¤U
¥t¥~ ªí®æ¤¤¨ú¥Xªº²~¼Æ¤Î½c¼Æ ¼Æ­È³£¤£¥¿½T,§Ú§â¥¿½Tµª®×¦C¥X,¤]½ÐÀ°¦£­×¥¿...¦A¦¸·PÁÂ
³Ì·s®w¦sA_2.rar (178.15 KB)

TOP

¦^´_ 22# PJChen

Sub ¼t¯Ê¶×Á`_¶×¤J()
Dim Arr, R&, ¼t¯Ê¼Æ&, ¤J¼Æ&, N&
Call ¼t¯Ê¶×Á`_²M°£
Arr = Range([­¸¤ñ!A1], [­¸¤ñ!BI65536].End(xlUp))
For R = 4 To UBound(Arr)
    ¼t¯Ê¼Æ = Val(Arr(R, UBound(Arr, 2)))
    ¤J¼Æ = Val(Arr(R, 7))
    If ¼t¯Ê¼Æ * ¤J¼Æ = 0 Then GoTo 101
    N = N + 1
    Arr(N, 1) = Arr(R, 6)
    Arr(N, 2) = Arr(R, 5)
    Arr(N, 3) = Arr(R, 8)
    Arr(N, 4) = ¤J¼Æ
    Arr(N, 5) = ¼t¯Ê¼Æ Mod ¤J¼Æ
    Arr(N, 6) = Int(¼t¯Ê¼Æ / ¤J¼Æ)
101: Next R
If N = 0 Then Exit Sub
With [¼t¯Ê¶×Á`!A3:F3].Resize(N)
     .Rows(1).Copy .Cells
     .Value = Arr
End With
End Sub


Sub ¼t¯Ê¶×Á`_²M°£()
With Sheets("¼t¯Ê¶×Á`")
    .UsedRange.Offset(3, 0).EntireRow.Delete
    .[A3:F3].ClearContents
End With
End Sub


============================

TOP

¤j¤j¦n,

³Ìªñ¦³¤@­Ó·sªºªí®æ»Ý¨D: ±N¼t¯Ê¸ê®Æ¶×Á`,±ø¥ó¬°¥H¤U,½Ð°ÝVBA­n¦p¦ó¼g?
1..        ­¸¤ñBIÄæ¤j©ó0ªº¸ê®Æ,¶ñ¤JA3:F
2..        A3:F¸ê®Æ­È¤Æ
3..        ±N²Ä3¦Cªº®æ¦¡¦V¤U½Æ»s
¼t¯Ê¶×Á`.rar (179.22 KB)

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-3-1 19:54 ½s¿è

¦^´_ 20# ­ã´£³¡ªL
­ã¤j,
°õ¦æ«áµo²{³øªí§ï¦¨¤F­q³æ²Î­p¦Ó¤£¬O¯Ê®Æªí,¥¦ªºÀ³§ì¨ú½d³ò¬OAP:BH

§Ú¥D­n¬O·Q¤F¸Ñ±z¤§«e¦^ÂЪºµ{¦¡
For C = 45 To UBound(Arr, 2)
    Ck = 0
    If Arr(3, C) = "¹º³æ¦X­p" Then Exit For  '¥[¤J³o¤@¦æ, ¥H[¹º³æ¦X­p]§PÂ_¼t¯ÊÄæ¦ìªºµ²§ôÂI
³o­Ó¤è¦¡,§Ú¸Õ¤£¥X¨Ó,·Qª¾¹D­n«ç»ò­×§ï

¥H¤U¬O¤§«eµ{¦¡runªºµ²ªG,§Ú¥u¬O·Q­n¥[¤J¬õ¦rªº³¡¥÷ & ÀÉ®× ¼t¯Êªí_amd3.rar (184.24 KB)

TOP

¦^´_ 19# PJChen

´ú¸ÕÀÉ:
¼t¯Ê_v02.rar (80.72 KB)

TOP

¦^´_ 15# ­ã´£³¡ªL
Dear­ã¤j,
­«·s¾ã²z¤@¤U...
1) °õ¦æ¼t¯Êªí«á·|¥X²{¦Û°Ê¥[Á`,§Ú·Q¥[­Ó"¦X­p",½Ð°Ý¦bµ{¦¡­þ­Ó¦a¤è¥i¥H¥[¤J?
2) ¼t¯Êªí.sheet AÄæ¥[¤J®Æ¸¹,¦pªG­n¤j´T­×§ïµ{¦¡¡B¤£¦n°µªº¸Ü,´Nºâ¤F¡I§Ú¦A¦Û¤v¤â°Ê¥[¤J
3) ¼t¯Êªí F:G(½c²~)¦p¦óÅý¥¦¦Û°Ê­pºâ«á,Åܦ¨­È,¤£­n¦³¤½¦¡?¥[Á`Äæ¤]¬O...

TOP

¦^´_ 1# PJChen

§Ú¤£·|VBA¡A¥u¯à¥Î¨ä¥L¤èªk°µ¥XÃþ¦üªºµ¹±z°Ñ¦Ò¡C
¥D­n¬O¥Î¸ê®Æ-±qªí®æ/½d³ò  §Q¥Î¥X³fsheet¤º®e¥HPower Query ½s¿è¾¹¥h¾ã²z¸ê®Æ¡A
³Ì«á¦A¥Î¼Ï¯Ã¤ÀªRªí¥h²£¥Í¸É³f©ú²Ó¡C
¥H¤W¡A¤j­P¤º®e¦pªþ¥ó
¦Û°Ê®Mªí-¬d¸ß»P¼Ï¯Ã¤ÀªR.zip (54.33 KB)

TOP

¦^´_ 15# ­ã´£³¡ªL
¸É¥R:
¼t¯Êªí.sheet AÄ檺®Æ¸¹,¤£ª¾¯à§_Åý¥¦¤@¨Ö¥X²{?

TOP

¦^´_ 15# ­ã´£³¡ªL
³o°}¤l¦]¬°§@·~¤º®e¦³ÅÜ°Ê,©Ò¥Hªí®æ¤]¤j´T­×§ï,½ÐÀ°¦£¬Ý¤U....
1)  For C = 45 To UBound(Arr, 2)
  §ï¦¨ For C = 45 To 60
³o­Ó¤èªk´ú¸Õ«áOK,
¦b°õ¦æ¼t¯Êªí«á·|¥X²{¦Û°Ê¥[Á`(¥Ø«e¦b²Ä8¦C)
§Ú·Q¥[­Ó"¦X­p",½Ð°Ý¦bµ{¦¡­þ­Ó¦a¤è¥i¥H¥[¤J?

2) ¥t¥~³o­Ó¤è¦¡,§ÚÁÙ¸Õ¤£¥X¨Ó,½Ð°Ý§Ú¥[¤Jªº¦a¤è¬O§_¤£¹ï?
For C = 45 To UBound(Arr, 2)
    Ck = 0
    If Arr(3, C) = "¹º³æ¦X­p" Then Exit For  '¥[¤J³o¤@¦æ, ¥H[¹º³æ¦X­p]§PÂ_¼t¯ÊÄæ¦ìªºµ²§ôÂI
3rd_¼t¯Êªí.rar (286.86 KB)

TOP

¦^´_ 14# PJChen

¬Ý¤£À´§Aªº°ÝÃD,
1)­Y­n¸ê®Æ©ñ¦b"¦Û°Ê¼t¯Êªí",
  ±N Sheets("¼t¯Êªí") §ï¦¨ Sheets("¦Û°Ê¼t¯Êªí")
2)¼t¯ÊÄæ¦ì¼Æ­Y¬O©T©wªº:
  For C = 45 To UBound(Arr, 2)
  §ï¦¨ For C = 45 To 60

©Î
For C = 45 To UBound(Arr, 2)
    Ck = 0
    If Arr(3, C) = "¹º³æ¦X­p" Then Exit For  '¥[¤J³o¤@¦æ, ¥H[¹º³æ¦X­p]§PÂ_¼t¯ÊÄæ¦ìªºµ²§ôÂI

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD