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

·Q°Ý§ÚªºEXCEL¤ñ¹ï¸ê®Æ­n«ç»ò¼g

·Q°Ý§ÚªºEXCEL¤ñ¹ï¸ê®Æ­n«ç»ò¼g

¦³¤H¥i¥H±Ð§ÚÀɮפ¤ªº¤ñ¹ï¸ê®Æ¾ã»ò¼g¶Ü?

¨D±Ï¤j®v ¬Ý¬Ý¯à¤£¯à±N§Úªº»Ý¨D¼g¥X¨Ó¡A¤u§@­n¥Îªº ¦ý­è±µÄ²EXCEL VBA¤£¤Ó·|

¤ñ¹ï¸ê®Æ¨D±Ï¤ñ¹ï¸ê®Æ¨D±Ï.rar (5.69 KB)

¯«µLªk«I¥Çªº¥@¬É

¦^´_ 1# tmde987
¨â¶gªº¸ê®Æ·|Åܰʪº¥u¦³¼Æ¶qÄæ¦ì¶Ü?

TOP

¨S¿ù !
¥D­n¬O­n²Î­p¤½¥q¬Y¨Çª««~ªºÅܰʶq
¨D±Ï¦U¦ì°ª¤âÀ°¦£¤@¤U ,ÁÂÁÂ!!
¯«µLªk«I¥Çªº¥@¬É

TOP

¦^´_ 3# tmde987


    ³o¦U¬O­n°µ¦w¥þ®w¦s¶Ü?
    ·Pı¤S¸ò¦w¥þ®w¦s¤£¤@¼Ë
   ¦]¬°§A¬O°w¹ï®ø¯Óªº³¡¤À¥hÁʶR
   ¦Ó¤£¬O¸É¨¬¦w¥þ®w¦s¶q

   ·Pı§Aªº°ÝÃDÀ³¸Ó¬O«Ü²³æ
   ¥i¬O§A­n±N¨BÆJ¤Á«Ü²Ó
   «ÜÃø©ú¥Õ§A­nªº
   
   ¦³ÂI§â²³æ°ÝÃD½ÆÂø¤Æ
   ¦pªG¬O­pºâ¦w¥þ®w¦s¥i¯à´N¦³¦Ò¶q¤Wªº®t²§
   ¦ý¬O¨Ì§Aªº¤è¦¡À³¸Ó¬O¦³§ó²³æªº³B²z¤èªk
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

©Î³\§a ! ¦ý¨CºØ¤£¦Pªº¤èªk¬O¦ÑÁ󵹧ڪº«Øij  
©Ò¥H§Ú§â¥¦¦C¥X¨Ó  ¨C­Ó°Ý¤À¶}¨Ó ²¦³º·s¤â¤£·|
µu®É¶¡­nºN¦³ÂIÃø ~~
¯«µLªk«I¥Çªº¥@¬É

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-1-8 00:09 ½s¿è

¦^´_ 5# tmde987
  1. Sub «ö¶s_1()
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  6.      d(a.Value) = a.Offset(, 4).Value
  7.   Next
  8.   For Each a In .Range(.[J2], .[J2].End(xlDown))
  9.      i = a.Offset(, 4).Value - d(a.Value)
  10.      If i <> 0 Then
  11.      ReDim Preserve Ar(s)
  12.      Ar(s) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, i, a.Offset(, 5).Value)
  13.      s = s + 1
  14.      End If
  15.   Next
  16. End With
  17. Sheet2.UsedRange.Offset(1) = "" '²Ä2¦C¥H¤U§R°£
  18. Sheet2.[A2].Resize(s, 6) = Application.Transpose(Application.Transpose(Ar)) 'A2¥H¤U¶ñ¤J
  19. End Sub
½Æ»s¥N½X
  1. Sub «ö¶s_2()
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  6.      d(a.Value) = a.Offset(, 4).Value
  7.   Next
  8.   For Each a In .Range(.[J2], .[J2].End(xlDown))
  9.      i = a.Offset(, 4).Value - d(a.Value)
  10.      If i <> 0 Then
  11.      ReDim Preserve Ar(s)
  12.      Ar(s) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 3).Value, i)
  13.      s = s + 1
  14.      End If
  15.   Next
  16. End With
  17. Sheet3.UsedRange.Offset(1) = "" '²Ä2¦C¥H¤U§R°£
  18. Sheet3.[A2].Resize(s, 4) = Application.Transpose(Application.Transpose(Ar)) 'A2¥H¤U¶ñ¤J
  19. End Sub
½Æ»s¥N½X
  1. Sub «ö¶s_3()
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  6.      d(a.Value) = a.Offset(, 4).Value
  7.   Next
  8.   For Each a In .Range(.[J2], .[J2].End(xlDown))
  9.      i = a.Offset(, 4).Value - d(a.Value)
  10.      If i <> 0 Then
  11.      ReDim Preserve Ar(s)
  12.      Ar(s) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 3).Value, i)
  13.      s = s + 1
  14.      End If
  15.   Next
  16. End With
  17. Sheet4.UsedRange.Offset(2) = "" '²Ä3¦C¥H¤U§R°£
  18. Sheet4.[A3].Resize(s, 4) = Application.Transpose(Application.Transpose(Ar)) 'A3¥H¤U¶ñ¤J
  19. End Sub
½Æ»s¥N½X
  1. Sub «ö¶s_4()
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  6.      d(a.Value) = a.Offset(, 4).Value
  7.   Next
  8.   For Each a In .Range(.[J2], .[J2].End(xlDown))
  9.      i = a.Offset(, 4).Value - d(a.Value)
  10.      If i <> 0 Then
  11.      ReDim Preserve Ar(s)
  12.      Ar(s) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, i, "=RC[-2]*RC[-1]")
  13.      s = s + 1
  14.      End If
  15.   Next
  16. End With
  17. Sheet5.UsedRange.Offset(2) = "" '²Ä3¦C¥H¤U§R°£
  18. Sheet5.[A3].Resize(s, 6) = Application.Transpose(Application.Transpose(Ar)) 'A3¥H¤U¶ñ¤J
  19. End Sub
½Æ»s¥N½X
  1. Sub «ö¶s_5()
  2. Dim Ar()
  3. With Sheet6
  4.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  5.      i = Application.Max(0, a.Offset(, 5).Value - a.Offset(, 4).Value) '­pºâÁʶR¼Æ¶q
  6.      ReDim Preserve Ar(s)
  7.      Ar(s) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, i)
  8.      s = s + 1
  9.   Next
  10. End With
  11. Sheet6.UsedRange.Offset(2, 8) = "" 'I3¥H¤U§R°£
  12. Sheet6.[I3].Resize(s, 5) = Application.Transpose(Application.Transpose(Ar)) 'I3¥H¤U¶ñ¤J
  13. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂHsieh ¤j¤jªº¸Ñµª

«ö¶s5 ³o¬q °õ¦æ·|¿ù»~

For Each a In .Range(.[A2], .[A2].End(xlDown))

§ÚÁ٬ݤ£¥X¨Ó¿ù»~ªº«ü¥O¦b­þ  ¬ã¨s«ü¥O¤¤ ÆZ²`¿Dªº!!
¯«µLªk«I¥Çªº¥@¬É

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD