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

¨Ì­q³æ¸ê®ÆÂà´«¦¨¤»¶g±Æµ{ªí¡A·q½Ð¦U¦ì¤j¤j½ç±Ð!!!

·PÁÂHsieh ª©¥D¡A¤p§Ì±N¥N½X½Æ»s­n°õ¦æ®É¡A¤S¦b"n = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1¡¨¦¹¦C¶]¥X"°õ¦æ¶¥¬q¿ù»~ '424': ¦¹³B»Ý­nª«¥ó"ªºÄµ§iµøµ¡¡A¬O°£¤F½Æ»s¥~ÁÙ¦³»Ý­n¦A°µ¤°»òªº³]©w¶Ü???

TOP

Hsieh ª©¥D¦]¤p§Ì¹ê»Ú±Æµ{¦³®É·|¦³¸ó¤ëªº°ÝÃD¡A¨Ò¡G»Ý¨D¤é5/20¡A¥æ´Á¦^ÂШì6/10¡A¦ý«ö¥Ø«e¥¨¶°°õ¦æµo²{¶È¤ñ¹ï"¤é"¦Ó¤w¡A¦p«ö¥H¤W¤é´Á¡A¨ä¥æ´Á¹ïÀ³ªº·|¨q¨ì5/10¡A¦¹¬O§_¥i¦A­×¥¿???

TOP

¦^´_ 12# p6703
  1. Sub Ex()
  2. Dim A As Range, x%, y%
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheets("Sheet2")
  5. s = Application.Min(.Rows(1))
  6. With Sheets("Sheet1")
  7. n = Application.Max(.Columns("E:F")) - s + 4
  8.    For Each A In .Range(.[A2], .[A2].End(xlDown))
  9. ReDim ar(0 To 1, 0 To n)
  10.        m = A & "," & A.Offset(, 1) & "," & A.Offset(, 2)
  11.        If IsEmpty(d(m)) Then
  12.        GoTo 10
  13.        Else
  14.        For i = 0 To 1
  15.          For j = 0 To n
  16.            ar(i, j) = d(m)(i, j)
  17.          Next
  18.        Next
  19.        End If
  20. 10
  21.        x = A.Offset(, 4) - s + 4 '»Ý¨D¤é
  22.        y = A.Offset(, 5) - s + 4 '¥æ´Á
  23.        For i = 0 To 2
  24.          ar(0, i) = A.Offset(, i)
  25.        Next
  26.        ar(0, 3) = "»Ý¨D¤é"
  27.        ar(1, 3) = "¥æ´Á"
  28.        ar(0, x) = ar(0, x) + A.Offset(, 3) '»Ý¨D
  29.        ar(1, y) = A.Offset(, 3)
  30.        d(m) = ar
  31.        Erase ar
  32.    Next
  33. End With
  34. r = 2
  35. For Each ky In d.keys
  36.   .Cells(r, 1).Resize(2, n + 1) = d(ky)
  37.   r = r + 2
  38. Next
  39. End With
  40. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥ý·PÁÂHsiehª©¥D¤F,©ú¤é¦A´ú¸Õ¬Ý¬Ý,¦ý¤W¤@­Ó°ÝÃD¤´·Q½Ð°Ý,´N¬O¦p³æ½Æ»s¥N½X,¦]¦ó°õ¦æ¥¨¶°·|¶]¥X¿ù»~©O...
Hsiehª©¥Dªº¥¨¶°,¤j¤j­°§C¤u§@¶q,¯uªº¤Ó·PÁ§A¤F

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-5-2 22:35 ½s¿è

¦^´_ 14# p6703

¦pªG¥H2010ª©¥»¶}±ÒÀɮ׮ɡA¦]¬°¤u§@ªíªºcodename¬O"¤u§@ªí1"¡B"¤u§@ªí2"...µ¥¡A¨Ã«D"Sheet1"¡B"Sheet2"...µ¥
©Ò¥H¥X¿ù¡C
½ÐÀˬd¤u§@ªíªºCodeName¬O§_¦s¦b?

³Ì«áªºµo©«¤¤¤w¸g§ï¦¨¨Ï¥Î¤u§@ªíªºNameÄÝ©Ê¡A­Y¦³¿ù»~½ÐÀˬd¤u§@ªí¦WºÙ¡C

¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂHsieh¥Sªº¦^ÂÐ,¤p§Ì«ö³Ì·sµ{¦¡½X°õ¦æ«á,«o¶]¥X"°õ¦æ¶¥¬q¿ù»~'9'¡A°}¦C¯Á¤Þ¶W¥X½d³ò"¡A¨Ã°±¦bar(0, x) = ar(0, x) + A.Offset(, 3)¦¹µ{¦¡½X¡A¦]§Ú²{°õ¦æªºµ§¼Æ¦³§Ö200µ§¡A¬O¦³³]­­µ§¼Æ¶Ü???

ÁÙ¦³­ì´£¨Ñ³øªí¤¤¶È¦³"­q³æ"¡A"®Æ¸¹"¡A"¶µ¦¸"¤T¸ê®Æ¡A¦ý¦pªG­n¦A¼W¥[´Xµ§¸ê®Æ¡A­ìµ{¦¡½X¬O§_§Y»Ý¦A­×§ï???

TOP

¦^´_ 16# p6703

§Ú²q·Q¬O§AªºSheet2!E2ªº¤é´Á¤ñ©Ò¦³­q³æ¤é´Áªº³Ì¤p­ÈÁÙ¤j¡A¤~·|²£¥Í³o¼Ëµ²ªG
½Ð¤W¶Ç¥X¿ùÀɮפ~¯àª¾¹D½T¹ê­ì¦]
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ªº½T¦³¦pHsiehª©¥D©Ò»¡ªºª¬ªp¡A¦ý§Y¨Ï§Ú±N¤é´Á³Ì¤p­È¤w§ó·s¦Ü5/1°_¡A¦ý°õ¦æ®É¤´µo¥Í¿ù»~¡Aªþ¤WÀɮסA·Ð½ÐHsiehª©¥D¸Ñ´b¡AÁÂÁÂ!!!
Book1.rar (15.27 KB)

TOP

¦^´_ 18# p6703
  1. Sub Ex()
  2. Dim A As Range, x#, y#
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheets("Sheet2")
  5. s = Application.Min(.Rows(1))
  6. With Sheets("Sheet1")
  7. n = Application.Max(.Columns("E:F")) - s + 4
  8.    For Each A In .Range(.[A2], .[A2].End(xlDown))
  9. ReDim ar(0 To 1, 0 To n)
  10.        m = A & "," & A.Offset(, 1) & "," & A.Offset(, 2)
  11.        If IsEmpty(d(m)) Then
  12.        GoTo 10
  13.        Else
  14.        For i = 0 To 1
  15.          For j = 0 To n
  16.            ar(i, j) = d(m)(i, j)
  17.          Next
  18.        Next
  19.        End If
  20. 10
  21.        x = A.Offset(, 4) - s + 4 '»Ý¨D¤é
  22.        y = A.Offset(, 5) - s + 4 '¥æ´Á
  23.        For i = 0 To 2
  24.          ar(0, i) = A.Offset(, i)
  25.        Next
  26.        ar(0, 3) = "»Ý¨D¤é"
  27.        ar(1, 3) = "¥æ´Á"
  28.        If x > 0 Then ar(0, x) = ar(0, x) + A.Offset(, 3) '»Ý¨DÁקKSheet1¤º»Ý¨D¤éµL¤é´Á
  29.        If y > 0 Then ar(1, y) = A.Offset(, 3) '¥æ´ÁÁקKSheet1¤º¥æ´ÁµL¤é´Á
  30.        d(m) = ar
  31.        Erase ar
  32.    Next
  33. End With
  34. r = 2
  35. For Each ky In d.keys
  36.   .Cells(r, 1).Resize(2, n + 1) = d(ky)
  37.   r = r + 2
  38. Next
  39. End With
  40. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¯u¬O¤Ó·PÁÂHsiehª©¥D¡A¸Ñ¨M¤F¤p§Ìªº¤j°ÝÃD¡A°õ¦æ¤w¥i¦Û°Ê®M¥X

¦pªG¤p§Ì·Q¦A¥[¤J¦h¤@¨Çªº¸ê®Æ¡]¨Ò­ì¥u¦³­q³æ,®Æ¸¹,¶µ¦¸¡A·Q¦A¼W¥[«È¤á,³W®æ...¡^==>¤w§ó·sªþ¥ó¤¤¡A½Ð°Ý¦p¦ó­×§ï???

¤p§Ì¦³¦Û¤v´ú¸Õ­×§ï¡A¦ý¥\¤O¹ê¦b¤£¨¬¡A§ï«áÁÙ¬OµLªk®M¥X¥¿½T¸ê®Æ¡A¬G·Ð½ÐHsiehª©¥DÀ°¦£

Book2.rar (16.01 KB)

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD