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

(¤w¸Ñ¨M)¿ù»~1004 ½Ð±Ð¤@¤U³o¤@¬qcode¬O§_¦³ÅÞ¿è¿ù»~

(¤w¸Ñ¨M)¿ù»~1004 ½Ð±Ð¤@¤U³o¤@¬qcode¬O§_¦³ÅÞ¿è¿ù»~

¥»©«³Ì«á¥Ñ m09903020 ©ó 2012-6-21 20:36 ½s¿è

¤£¦n·N«ä¡A½Ð°Ý³o¤@¬q¦³1004À³¥Îµ{¦¡©Îª«¥ó©w¸q¤Wªº¿ù»~ ...
¤£ª¾¹D¬O¤°»ò°ÝÃD?QQ
  1. Sub SAMAD()
  2. Dim myRange As Range
  3. For i = 1 To 299
  4. For Each c In Sheets("batch_pool").Range("A1:A" & [a1].End(xlDown).Row)'§ì¨ú5/10/50µ§integer¸ê®Æ
  5. ordernum = Sheets("order_pool").Cells(i, 1)'§ì¨ú299µ§¸ê®Æ¦ì§}
  6. Dim distance, SRD'¶ZÂ÷¤½¦¡¥Î
  7. distance = 0
  8. SRD = 0
  9. If (ordernum > 0) And (ordernum < 3001) Then
  10.     For j = 1 To 5
  11.     temp = Worksheets("Á`­q³æ").Cells(ordernum + 1, j + 6) 'temp is ­q³æ¤¤¨Cµ§item¥N¸¹
  12.     distance = Sheets("Àô¹Ò¶ZÂ÷").Cells(c, temp + 2) '§Q¥Î¶ZÂ÷ªí¹ïÀ³¥X¤w¶ZÂ÷
  13.     SRD = distance + SRD 'summation's idea by formula
  14.     Next j
  15.     SRD = SRD / 5 'SRD of SAMAD's fomula
  16.     Sheets("order_pool").Cells(i, 2) = SRD 'put into sheet
  17.    
  18. ElseIf (ordernum >= 3001) And (ordernum < 6001) Then '10 items
  19.     For j = 1 To 10
  20.     temp = Worksheets("Á`­q³æ").Cells(ordernum + 1, j + 6)
  21.     distance = Sheets("Àô¹Ò¶ZÂ÷").Cells(c, temp + 2)
  22.     SRD = distance + SRD
  23.     Next j
  24.     SRD = SRD / 10
  25.     Sheets("order_pool").Cells(i, 2) = SRD
  26.    
  27. ElseIf (ordernum >= 6001) And (ordernum < 9001) Then '50 items
  28.     For j = 1 To 50
  29.     temp = Worksheets("Á`­q³æ").Cells(ordernum + 1, j + 6)
  30.     SRD = distance + SRD
  31.     Next j
  32.     SRD = SRD / 50
  33.     Sheets("order_pool").Cells(i, 2) = SRD
  34. Else
  35.         MsgBox "error³o»ò¤E«Ü©_©Ç£°§A"
  36. End If
  37. Next
  38. Next i
  39. Set myRange = Worksheets("order_pool").Range("B1:B299")
  40.     result = Application.WorksheetFunction.min(myRange)
  41. If (result.Address.Row > 0) And (result.Address.Row < 3001) Then
  42. Call cal_vol(ByVal 5)
  43. ElseIf (result.Address.Row >= 3001) And (result.Address.Row < 6001) Then
  44. Call cal_vol(ByVal 10)
  45. ElseIf (result.Address.Row >= 6001) And (result.Address.Row < 9001) Then
  46. Call cal_vol(ByVal 50)
  47. Else
  48. MsgBox "error³o»ò¤E«Ü©_©Ç£°§A"
  49. End If
  50. End Sub
½Æ»s¥N½X
ÁÂÁ¦U¦ì

result = Application.WorksheetFunction.Min(myRange)
If (result.Address.Row > 0) And (result.Address.Row < 3001) Then
³o¸Ì´N¤£¦XÅÞ¿è¡A¨S¦³³o¼Ëªí¹Fªº¡I
§AÀ³¸Ó¦b¤u§@ªí¤W´ú¸Õ¡A¬Ý¿ù»~¤¤Â_¦b­þ¸Ì
¥ú¬Ý¥N½X¨S¦³¤å¥ó«ÜÃø§ä¥X©Ò¦³°ÝÃD¡C

TOP

¦^´_ 2# oobird

http://dl.dropbox.com/u/16247104/10000%20order_sortingalready.xlsm
¦]¬°µ{¦¡¹L¤j

TOP

¦^´_ 3# m09903020
«Øij ¼Ò²Õ³»ºÝ¥[¤W   Option Explicit  ±j¨î«Å§iÅܼƫ¬ºA
À°§A§ä§ä¨ì¤@³B distance = Sheets("Àô¹Ò¶ZÂ÷").Cells(c.Row, temp + 2).Value '§Q¥Î¶ZÂ÷ªí¬M®g¥X¶ZÂ÷
Ä~Äò¤U¥h¦] 2003ªºÄæ¦ì¥u¦³ 256  µLªk¦A°£¿ù
½Ð¨Ì¤U­±µ{¦¡³]¤¤Â_ÂI°£¿ù
  1. Sub SAMAD()
  2. Dim myRange As Range, c, ordernum, temp As Integer, result
  3. On Error GoTo ex: '---¥[¤W¦¹¦æ
  4. For i = 1 To 299
  5. For Each c In Sheets("batch_pool").Range("A1:A" & [a1].End(xlDown).Row)
  6. '
  7. '
  8. '
  9. '
  10. ElseIf (result.Address.Row >= 6001) And (result.Address.Row < 9001) Then
  11. Call cal_vol(ByVal 50)
  12. Else
  13. MsgBox "error³o»ò¤E«Ü©_©Ç£°§A"
  14. End If
  15. '-------µ{¦¡©³³¡---------------------------
  16. Exit Sub '---¥[¤W¦¹¦æ
  17. ex:           '---¥[¤W¦¹¦æ
  18. Resume   '---¥[¤W¦¹¦æ''·Æ¹«¿ï³o¸Ì«ö F9 ³]¤U¤¤Â_ÂI ¦³¿ù»~  «öF8 ¨Ó°£¿ù
½Æ»s¥N½X

TOP

¦^´_ 4# GBKEE


   ÁÂÁ¤j¤j¸Ñ´b¡A§Ú¦A ¥Î Option Explicit debug¡C

TOP

¦^´_ 2# oobird


    ÁÂÁÂOO¤j¡A§Ú¥Î±z«Øijªº¤è¦¡­«¼g µo²{§Ú¿ù¦b for each ªºc  §ï¦¨¥Î¼g¦ºªºfor°j°é¶]´N¨S°ÝÃD¤F

   ÁÙ¦³¨º"¶Ã´úªº"´X¦æ§PÂ_¦¡§Úµo²{¬O¦Û¤v·d¿ùF1ªº·N«ä¡C

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD