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

µ{¦¡¸É¥R

µ{¦¡¸É¥R

Load data.rar (53.02 KB)

½Ð±Ðªþ¥ó¸Ìªºµ{¦¡¦p¦ó¸É¥R:

¦pªG²Å¦X±ø¥óªÌ,±N¨Ó·½¸ê®Æªº¨º¤@¦C,¥[¤W¦³ÃC¦âªº©³¦â,¨Ã±N¼Æ¦r¼g¶i¥Øªº¤u§@ªí,

If Val(search_PL) = Val(current_PL) And Val(search_cust_ID) = Val(current_cust_ID) Then
        Worksheets("TC").Cells(fcsty, start_col_in_TC).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 7).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 1).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 8).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 3).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 9).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 4).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 10).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 5).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 11).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 50).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 14).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 51).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 15).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 52).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 16).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 53).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 17).FormulaR1C1)
        Worksheets("TC").Cells(fcsty, start_col_in_TC + 54).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 18).FormulaR1C1)
End If
fangac

¦^´_ 1# fangsc
  1. Sub Loaddata()
  2. Dim fcsty, bklogy As Integer
  3. Dim current_PL, current_cust_ID As String
  4. Dim search_PL, search_cust_ID As String
  5. Dim TC_Maxrow As Integer
  6. Dim Backlog_maxrow As Integer
  7. Dim start_col_in_TC As Integer
  8. Dim PL_index_col, CID_index_col As Integer
  9. '-----------------------------
  10. Dim Ar(1 To 2), xi As Integer, MyColorIndex As Integer
  11. '-----------------------------
  12. PL_index_col = 1
  13. CID_index_col = 3
  14. TC_Maxrow = 16
  15. Backlog_maxrow = 17
  16. start_col_in_TC = 58
  17. '----------------------------------------------
  18. Ar(1) = Array(0, 1, 3, 4, 5, 50, 51, 52, 53, 54)
  19. Ar(2) = Array(7, 8, 9, 10, 11, 14, 15, 16, 17, 18)
  20. MyColorIndex = 7                       '©³¦â ¼Æ­È
  21. '----------------------------------------------
  22. Worksheets("TC").Activate
  23. For fcsty = 2 To TC_Maxrow
  24.     search_PL = Worksheets("TC").Cells(fcsty, 16) '.FormulaR1C1
  25.     search_cust_ID = Worksheets("TC").Cells(fcsty, 13) '.FormulaR1C1
  26.     'Worksheets("1").Select
  27.     For bklogy = 2 To Backlog_maxrow
  28.         current_PL = Worksheets("1").Cells(bklogy, PL_index_col) '.FormulaR1C1
  29.         current_cust_ID = Worksheets("1").Cells(bklogy, CID_index_col) '.FormulaR1C1
  30.         If Val(search_PL) = Val(current_PL) And Val(search_cust_ID) = Val(current_cust_ID) Then
  31.         ' ±ø¥ó²Å¦X®É,±Nworksheet "1"ªºcurrent_PL ¤Î current_cust_ID ¥[¤W©³¦â,¦A°õ¦æ¥H¤U¼g¤J¸ê®Æ
  32.             Worksheets("1").Cells(bklogy, PL_index_col).Interior.ColorIndex = MyColorIndex
  33.             Worksheets("1").Cells(bklogy, CID_index_col).Interior.ColorIndex = MyColorIndex
  34.             For xi = 0 To UBound(Ar(1))
  35.                 Worksheets("TC").Cells(fcsty, start_col_in_TC + Ar(1)(xi)) = Val(Worksheets("1").Cells(bklogy, Ar(2)(xi)))
  36.             Next
  37.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 7).FormulaR1C1)
  38.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 1).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 8).FormulaR1C1)
  39.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 3).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 9).FormulaR1C1)
  40.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 4).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 10).FormulaR1C1)
  41.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 5).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 11).FormulaR1C1)
  42.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 50).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 14).FormulaR1C1)
  43.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 51).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 15).FormulaR1C1)
  44.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 52).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 16).FormulaR1C1)
  45.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 53).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 17).FormulaR1C1)
  46.             ' Worksheets("TC").Cells(fcsty, start_col_in_TC + 54).FormulaR1C1 = Val(Worksheets("1").Cells(bklogy, 18).FormulaR1C1)
  47.         End If
  48.     Next 'bklogy
  49. Next 'fcsty
  50. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE

·PÁª©¥Dªº­×¥¿»P¸É¥R.
¬d¤F "ubound"  ¬O¨ú±o¬O¼Æ组ªº¤¸¯À¼Æ
¾Ç¨ì¤F"ubound"ªº¥Îªk. ÁÂÁ§A.
fangac

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD