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

[µo°Ý] ¹j¦C½Æ»s¸ê®Æ¦Ü¥t¤@¤u§@ªí¡A¨Ì¹ïÀ³Äæ¦ì¶K¤W

[µo°Ý] ¹j¦C½Æ»s¸ê®Æ¦Ü¥t¤@¤u§@ªí¡A¨Ì¹ïÀ³Äæ¦ì¶K¤W

¥»©«³Ì«á¥Ñ viggostar ©ó 2010-5-24 23:56 ½s¿è

¦U¦ì°ª¤â¦n¡AªþÀɬO§Ú°µªºsampleÀÉ

detail¤u§@ªí¬°¸ê®Æ¨Ó·½
Order¤u§@ªí«h¬O­q®Æ³æ

¤º§tªº¥¨¶°»yªk¬O
±Ndetail¤u§@ªí¤¤ªº¸ê®Æ¨ÌCÄ檺¤H­û¿ï¥X¥LÁÙ¨S­q¹s¥óªº¶µ¥Ø
¨Ã±N¦U­Ó§Ú»Ý­nªº¸ê®Æ¤@¤@½Æ»s¨ìorder¤u§@ªíªº¹ïÀ³Äæ¦ì¤¤

§Ú¦³·Q¹L¥Î (sheet1).range("a3").value=(sheet3).range(q3).value ¤§Ãþªº¼gªk, ´N¤£»Ý­nselect.copy¨º»ò¦h¦¸
                      ¡ô¥u¬O¦L¶H¤¤°O±o³o»ò¼g, ®Ñ¤£¦b¤âÃä
¦ý¿z¿ï«áªº¸ê®Æ¨Ã¤£¬O¬Û¾Fªº¦C, ¤£ª¾¹D»yªk¸Ó«ç»ò§ï¼g

§Ú·Q°µªº¥\¯à (¦p"¦¨ªG"¤u§@ªíÅã¥Ü)
1. ¦p¤W©Ò»¡, ±Ndetail¤u§@ªí¤¤¿z¿ï¦nªº¸ê®Æ, ¨Ì¤£¦PÄæ¦ì½Æ»s¨ìorder¤u§@ªíªº¹ïÀ³Äæ¦ì¤¤
2. ½Æ»s²Ä¤G­Ó¤Hªº¸ê®Æ«á, ¦Û°Ê¶K¨ì³Ì«á¤@¦C¸ê®Æªº¤U¤@¦C

·PÁ¦U¦ì°ª¤âÀ°¦£ Sample.rar (9.39 KB)

¦^´_ 1# viggostar
  1. Sub Ex()
  2. Dim Ar(), Ay(6), Rng As Range, CLM$, A As Range, i%, s&
  3. With Sheet1
  4. CLM = "CLKMPY"
  5. Set Rng = .Range(.[C3], .[C65536].End(xlUp)).SpecialCells(xlCellTypeVisible)
  6. For Each A In Rng
  7. For i = 1 To 6
  8.   Ay(i - 1) = .Cells(A.Row, Mid(CLM, i, 1)).Value
  9. Next
  10.    ReDim Preserve Ar(s)
  11.    Ar(s) = Ay
  12.    s = s + 1
  13. Next
  14. End With
  15. Sheet2.[A65536].End(xlUp).Offset(1, 0).Resize(s, 6) = Application.Transpose(Application.Transpose(Ar))
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

·PÁªO¥Dªº¦^ÂÐ
¥t¦³¨Ç°ÝÃD¦A½Ð±Ð¤@¤U
1. CLM = "CLKMPY" ³o³¡¥÷¡A¦b§Ú¹ê»ÚªºÀɮפ¤¡A½Æ»sªº¬OV¡BAA¡BAD¡BAOÄæ
      ¹Á¸Õ±N""¸Ìªº­È§ï¦b"VAAADAY"©Î"V" & ""AA" & "AD" & "AO"¡A±a¥X¨Óªº­È³£¤£¹ï¡A½Ð°Ý§Ú¸Ó¦p¦ó³]©w©O¡H
2. ¹ê»ÚÀɮפ¤"order"ªºaÄ榳¼Æ¦r¡A­Y¨Ï¥ÎªO¥D±zªºµ{¦¡½X¡A¸ê®Æ·|¶K¦b"order"¤u§@ªíªº²Ä19¦C~
    ¥B²Ä1¶µ¤¤¶K¤WªºÄæ¦ì¦border¤u§@ªí¤¤¨Ã¤£³sÄò¡A¸Ó¦p¦ó¸Ñ¨M©O¡H
(¤µ¤Ñ¤W¶Çªº¤w¬O¹ê»Ú¨Ï¥ÎªºÀÉ®×)
·PÁÂÀ°¦£¡I

Report_SAMPLE.rar (206.01 KB)

TOP

¦^´_ 3# viggostar
  1. Sub Ex()
  2. Dim Ar(), Ay(4), Rng As Range, Ck(), Kr(), A As Range, i%, s&
  3. With Sheet1
  4. Ck = Array("V", "AD", "AO", "AA")
  5. Kr = Array("C", "E", "H", "K")
  6. Set Rng = .Range(.[C5], .[C65536].End(xlUp)).SpecialCells(xlCellTypeVisible)
  7. For Each A In Rng
  8. For i = 0 To 3
  9.   Ay(i) = "'" & .Cells(A.Row, Ck(i))
  10. Next
  11.    ReDim Preserve Ar(s)
  12.    Ar(s) = Ay
  13.    s = s + 1
  14. Next
  15. End With
  16. For i = 0 To UBound(Ar)
  17. Set A = Sheet3.[C65536].End(xlUp).Offset(1, 0)
  18.    For j = 0 To 3
  19.    Sheet3.Cells(A.Row, Kr(j)) = Ar(i)(j)
  20.    Next
  21. Next
  22.    
  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


¥i¥H¤F¡A·PÁªO¥D¡I

¦pªG³Â·Ð±z³v¦æ¸Ñ»¡µ{¦¡½Xªº·N«ä¡A¬O¤£¬O·|¤Ó¹L¥÷ >"<

§Úª¾¹D¦³¥Î¨ì©w¸q¡B°j°é

¦ý redim preserve ªº·N«ä¤£¤F¸Ñ

¬°¤°»ò¥i¥H¥u¦³with sheet1¨Ó°õ¦æ

¤£»Ý­n«ü©wsheetªº¦W¦r¬O order ©Î detail

²Ä¤@¦æªºi¡Bs«á¬°¤°»ò­n¥[%¡B&§¹¥þ¤£À´¡K

¯uªº«Ü¤£¦n·N«ä³Â·Ð§A

­Y±z¦³ªÅ¦A³Â·Ð§A¦^µª§Ú

·PÁ±z~

TOP

¦^´_ 5# viggostar


    Preserve ¿ï¾Ü©Ê¤Þ¼Æ¡C·í§ïÅܭ즳°}¦C³Ì«á¤@ºûªº¤j¤p®É¡A¤´µM«O¦³­ì¨Óªº¸ê®ÆªºÃöÁä¦r¡C
¤]´N¬O»¡°}¦C¤j¤p§ïÅÜ«á,¤´«O¯d­ì¦³ªº¤¸¯À
¨Ò¦p«Å§i¤@°}¦CAr()
­Y¥ÎRedim Ar(5)
¨º¤£ºÞ¤§«eµ¹¹LAr(0),Ar(1),...¦³¬Æ»ò­È
¸gReDim¥H«á³o¨Ç­È´N¥þ³¡³Q²M°£¤F
                                                         
Dim Ar(4)
for i=0 to 3
   Ar(i)=i
next
¤W­±±Ô­z§A¤w¸g±N­ì°}¦CAr(0)~Ar(3)µ¹¹L­È
²{¦b­nÂX¤j¦¨5­Ó¤¸¯À¦ý­n«O¯dAr(0)~Ar(3)ªº­È
ReDim Preserve Ar(5)
Ar(4)=4
§A¦A´ú¸Õ
for i= 0 to UBound(Ar)
    msgbox "Ar(" & i & ")=" & Ar(i)
next
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# viggostar


    ¬°¤°»ò¥i¥H¥u¦³with sheet1¨Ó°õ¦æ
¤£»Ý­n«ü©wsheetªº¦W¦r¬O order ©Î detail
Sheet1¬O¥Î¤u§@ªíªºCodeName¤£·|¦]¤u§@ªí§ó¦W¦Ó§ïÅÜ
order ©Î detail¬O¤u§@ªíªºName·|¦]¤u§@ªí§ó¦W¦Ó§ïÅÜ
                                                                                             
Åܼƪº¸ê®Æ«¬ºA
¦b Visual Basic ¥i¥Î²Å¸¹ (&) ªº«¬ºA«Å§i¦r¤¸¨Óªí¥Ü¤@­Ó Long
¦b Visual Basic ¥i¥Î¦Ê¤À¤ñ²Å¸¹ (%) ªº«¬ºA«Å§i¦r¤¸¨Óªí¥Ü¤@­Ó Integer¡C
¦b Visual Basic ¥i¥Î @ «Å§i¦r¤¸¨Óªí¥Ü Currency
¦b Visual Basic ¤¤¥i¥Î«¬ºA«Å§i¦r¤¸ (!) ¨Óªí¥Ü¤@­Ó Single¡C
¦b Visual Basic¡A²Å¸¹ (#) ¬Oªí¥Ü Double ªº«¬ºA«Å§i¦r¤¸
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh
¯Â°Ñ¦Ò ¥i¥H²¤Æ¤@¤Uµ{¦¡½X
Sub Ex()
    Dim Ck(), Kr(), i%
    Ck = Array("V", "AD", "AO", "AA")
    Kr = Array("C", "E", "H", "K")
    With Sheet1
        For i = 0 To UBound(Ck)
            With .Range(Ck(i) & 5, .Range(Ck(i) & 65536).End(xlUp)).SpecialCells(xlCellTypeVisible)
                .copy Sheet3.Range(Kr(i) & 65536).End(xlUp).Offset(1, 0)
            End With
        Next
    End With
End Sub

TOP

«D±`·PÁÂHsiehª©¥Dªº¸Ñ»¡¡AÁٳ·бzµ¹¤F¨º»ò¦h¸ê®Æ
¶X°²¤é­è¦n§V¤OÆp¬ã

·PÁ¡I^^
ps. GBKEEª©¥Dªº§Ú¤]·|¸Õ¸Õ¬Ý, ÁÂÁ³á^^

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD