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

[µo°Ý] EXCEL¦h­Ó¤u§@­¶¸ê®Æ·J¾ã

  1. Sub ex()
  2. Dim Ar(0 To 7), A As Range, C As Range, TCode, Cnt
  3. Ar(0) = Array("«È¤á¥N¸¹", "«È¤á§å¸¹", "Icode", "¿é¤J¶q", "TCode", "B1", "B1¥¢±Ñ", "B2", "B2¥¢±Ñ", "B3", "B3¥¢±Ñ", "B4", "B4¥¢±Ñ")
  4. Set A = Sheet2.[F:F].Find("A120004", lookat:=xlWhole)
  5. If Not A Is Nothing Then
  6.    For i = -3 To 3
  7.       TCode = A.Offset(i, 7).Value
  8.       Set C = Sheet1.[D:D].Find(TCode, lookat:=xlWhole)
  9.       b1 = C.Offset(, 4).Value
  10.       b2 = C.Offset(, 6).Value
  11.       b3 = C.Offset(, 8).Value
  12.       b4 = C.Offset(, 10).Value
  13.       Cnt = A.Offset(i, 4).Value
  14.       If Not C Is Nothing And TCode <> "" Then
  15.       Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)
  16.          Else
  17.       Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, "NA#", "NA#", "NA#", "NA#", "NA#", "NA#", "NA#", "NA#")
  18.       End If
  19.   Next
  20. End If
  21. With Sheet3
  22. .[A:M].ClearContents
  23. For i = 7 To 13
  24. .Columns(i).NumberFormat = "0.00%"
  25. Next
  26. .[A1].Resize(8, 13) = Application.Transpose(Application.Transpose(Ar))
  27. End With
  28. End Sub
½Æ»s¥N½X
¦^´_ 10# jcchiang
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 11# Hsieh


    ³Ì«á¤@­Óµ{¦¡½X¥i¥H±N0§ï¦¨NA,¦ý²Ä2ºØ±¡ªpµLªk°õ¦æ(¦³TCode¦bSheet1¬dµL¸ê®Æ)
    ¥Ø«e±NB1~B4¸ê®Æ§ï¦¨
  If Not C Is Nothing And TCode <> "" Then b1 = C.Offset(, 4).Value
   If Not C Is Nothing And TCode <> "" Then b2 = C.Offset(, 6).Value
   If Not C Is Nothing And TCode <> "" Then b3 = C.Offset(, 8).Value
   If Not C Is Nothing And TCode <> "" Then b4 = C.Offset(, 10).Value
    «h¥i°õ¦æ²Ä2ºØ±¡§Î
   ·PÁÂH¤j­@¤ßªº«ü¾É,ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD