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

[µo°Ý] ½Ð¤j®aÀ°§Ú¬Ý³oµÛ¸ê®Æ½s±Æ¶¶§Çªº°ÝÃD

[µo°Ý] ½Ð¤j®aÀ°§Ú¬Ý³oµÛ¸ê®Æ½s±Æ¶¶§Çªº°ÝÃD

½Ð°Ý¤j®a§Ú­n«ç»ò§â²Ä70¦Cªº¸ê®Æ¥ÎVBA¼g¥X¨Ó©O?
½Ð¬Ý§Úªºªþ¥ó»¡©ú¡AÁÂÁÂ!

°ÝÃD1215.zip (72.89 KB)

¦]¬°§Aªºªþ¥ó¬Ý¤£¥X§A¯u¥¿­n§ìªº¸ê®Æ¬O¨º¤@¦C? (Priority1¡BPriority2¡BPriority3 ÁÙ¬O Vendor?)
¬Gµ{¦¡¤¤ª½±µ·í§@§A¬O­n§ì Title ¨º¤@¦C, §A¥u­nÅܧó§ì¸ê®Æªº¦C¸¹§Y¥i§ì¨ú¬Û¹ïÀ³¦C¤¤ªº¤W­z¸ê®Æ¤F.

¥H³o­Ó¨Ò¤l¨Ó»¡¨ä¹ê¥u­n¥ý¨ú±o Max¡BMin »P Middle ªº¶}ÀYÄ渹,
¦A¨Ì¾Úªí®æ³°Äò§ì¨ú­Ó§O¸ê®Æªº­Ó¼Æ·f°t°_¨Ó´N¥i¥H¤F.

Sub main()
  Dim iMax%, iMin%, iMid%, iColumn%, iRow%
  Dim iNum%, iI%
  Dim vDatabase, vOutput

  Set vDatabase = Sheets("DATABASE")
  Set vOutput = Sheets("OUTPUT")
  iMax = 182
  iColumn = iMax
  iRow = 70

  With vDatabase
    While Left(.Cells(1, iColumn), 3) = "Max"
      iColumn = iColumn + 1
    Wend
    iMin = iColumn
  
    While Left(.Cells(1, iColumn), 3) = "Min"
      iColumn = iColumn + 1
    Wend
    iMid = iColumn
  End With
  
  iColumn = 3
  With vOutput
    For iNum = 9 To 12
      For iI = 0 To .Cells(2, iNum) - 1
         .Cells(iRow, iColumn) = vDatabase.Cells(1, iMax)
        iMax = iMax + 1
        iColumn = iColumn + 1
      Next iI
   
      For iI = 0 To .Cells(3, iNum) - 1
        .Cells(iRow, iColumn) = vDatabase.Cells(1, iMin)
        iMin = iMin + 1
        iColumn = iColumn + 1
      Next iI
      For iI = 0 To .Cells(4, iNum) - 1
        .Cells(iRow, iColumn) = vDatabase.Cells(1, iMid)
        iMid = iMid + 1
        iColumn = iColumn + 1
      Next iI
    Next iNum
  End With
End Sub

TOP

¦^´_ 2# luhpro


½Ð±Ðuhpro...
±z¦^ÂЪº¸Ñµª¡A§Ú¸Õ¶]¹L¤F¡A¸ê®Æ¬O±qDATABASE sheet¨ºÃä§ì¹L¨Óªº¡A
½Ð°Ý§Ú¦pªG­n¦b²Ä71-74¦Cªº¦a¤è¡A§âPriority1-3 & Vendorªº¸ê®Æ¤@°_¶ñ¤JOUTPUT sheet¡A
½Ð°Ý§Ú¸Ó«ç»ò°µ©O?

¥t¥~¤@­Ó°ÝÃD¡A¥Ø«e§Úªº¾÷¾¹¼Æ¶q¬O4¥x¡A¦pªG­n¼W¥[¨ìn¥x¡A¬O¤£¬O¦b³o¸ÌFor iNum = 9 To 12 ¡÷§â12§ï¦¨(n+8)¶Ü?

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-12-15 15:28 ½s¿è

¦^´_ 3# sandra_wang
Ãö©óL2¬°3ªº½s½X¬O§_­n¦³3­ÓMAX
¸Õ¸Õ¬Ý
  1. Sub Ex()
  2. Dim A As Range, MyStr$
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. k = 182
  7. With Sheets("DATABASE")
  8.     Do Until .Cells(1, k) = ""
  9.     Set A = .Cells(1, k)
  10.        d(A.Value) = A.Resize(5, 1).Value
  11.        k = k + 1
  12.     Loop
  13. End With
  14. With Sheets("OUTPUT")
  15. k = 9
  16. Do Until .Cells(1, k) = ""
  17. MyStr = Replace(.Cells(1, k), "_", "")
  18.    Set A = .Cells(2, k).Resize(3, 1)
  19.    s = 1
  20.    For j = 1 To 3
  21.    mykey = IIf(j = 1, "Max_", IIf(j = 2, "Min_", "Middle_"))
  22.       For i = 1 To A(j)
  23.          d1(mykey) = d1(mykey) + 1
  24.          d2(MyStr & "_" & s) = d(mykey & d1(mykey))
  25.          s = s + 1
  26.       Next
  27.    Next
  28.    k = k + 1
  29. Loop
  30. k = 3
  31. For Each ky In d2.keys
  32. .Cells(6, k) = ky
  33. .Cells(70, k).Resize(5, 1) = d2(ky)
  34. k = k + 1
  35. Next
  36. End With
  37. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2010-12-15 23:10 ½s¿è

¦^´_ 3# sandra_wang
¡° ¥H¤Uªº ixxx ªí iMax¡BiMin »P iMiddle

¶â... ¥ý±N iRow = 70 §ï¦¨ iRow = 71
¦A±N  .Cells(iRow, iColumn) = vDatabase.Cells(1, ixxx)

§ï¦¨¥|¦æ
.Cells(iRow, iColumn) = vDatabase.Cells(2, ixxx)
.Cells(iRow + 1, iColumn) = vDatabase.Cells(3, ixxx)
.Cells(iRow + 2, iColumn) = vDatabase.Cells(4, ixxx)
.Cells(iRow + 3, iColumn) = vDatabase.Cells(5, ixxx)

©Î¬O§ï¦¨
Dim iNum%, iLeft%, iI%, iJ%
.
.
.
For iJ = 0 To 3
   .Cells(iRow + iJ, iColumn) = vDatabase.Cells(iJ + 2,  ixxx)
Next iJ

¦Ü©ó
For iNum = 9 To 12

½T¹ê¥u­n§ï¦¨
.
.
iLeft = 9
.
.
.
For iNum = iLeft To iLeft + 8

§Y¥i.

§Úªºµ{¦¡¬O¤ñ¸ûª½Æ[,

Hsieh ¤j¤jªºµ{¦¡³£«Ü²¼ä,

§A¥i¥H°Ñ¦Ò¬Ý¬Ý­n¥Î­þ­Ó.

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD