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

[µo°Ý] ¦p¦ó¨Ï¥ÎVBA©Î¨ç¼Æ°µ¦h­Ó°}¦C¼Æ­Èªº¤ñ¸û¤Î®æ¦¡¤Æ±ø¥óªº³]©w

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-12-17 16:45 ½s¿è

¦^´_ 10# cmo140497
§Aªº¸ê®ÆÅã¥Ü¬O2003ª© ¦ó¤£¦b2003¸Õ¬Ý
´ú¸Õ12# 2010ª© »Ý¬O
  1. .Columns(Columns.Count).AdvancedFilter xlFilterCopy, , .Cells(1, Columns.Count - 1), Unique:=True
½Æ»s¥N½X

TOP

¦^´_ 11# GBKEE


    ¤p§Ì¬O¥Îoffice 2010 plusª©,¦A³Â·Ðª©¥DÀ°¤p§Ì¬Ý¤@¤U,·P®¦

´M§ä¦U²Õ¼Æ¬Û¦P¦ì¤§¬O§_­«ÂЩοW¥ß¤§¼Æ­È§@¦Ê¤À¤ñ®æ¦¡¤Æ±ø¥ó_¤w¦^ÂÐ_20121217_1556.zip (40.28 KB)

TOP

¦^´_ 11# GBKEE


    Dear ª©¥D:
À³¸Ó¬O¥i¥H¤F,¤p§Ì±oªáÂI®É¶¡¬Ý¤@¤U,¤]§Æ±æª©¥D¥i¥H¤£§[«ü¾É,¥t¥~¤p§Ì¦pªG·Q§âTotalµ²ªG¥á¦b®ÇÃ䪺¦ì§}(AI5:BF28),¤£ª¾¬O§_¥i¦æ?¤ÎÃC¦âªº­pºâ¦n¹³¤Ï¤F,¤Î¦pªG§¡¬°0,¤£ª¾¬O§_¥i§e²{¥N½X1ªºÃC¦â,¤£ª¾¬O§_¥i¦A¦æ­×¥¿?
ÁÙ¬O«Ü·PÁª©¥D±zªº¤j¤Oªº¨ó§U,·P®¦!



´M§ä¦U²Õ¼Æ¬Û¦P¦ì¤§¬O§_­«ÂЩοW¥ß¤§¼Æ­È§@¦Ê¤À¤ñ®æ¦¡¤Æ±ø¥ó_¤w¦^ÂÐ_­×¥¿1.zip (40.28 KB)

TOP

¦^´_ 4# Hsieh


    Dear Hsieh ª©¥D¤j¤j:
¤£¦n·N«ä,¦A«×¥´ÂZ±z¤F,Ãö©ó¤p§Ìªº°ÝÃD,¤£ª¾±z¬O§_¦³§ó¦nªº§@ªk,±qGBKEEª©¥Dªº§@ªk,°õ¦æ³t«×¤W·|¦³ÂIºC,¦pªG¤è«Kªº¸Ü,¤p§Ì±N¬yµ{§ó²¤Æ,ª½±µ­p¼Æ¤£¬°0¤§Count,°£¥H®Ö¨ú¤è¶ô¼Æ,­pºâ¦Ê¤À¤ñ±ø¥ó®æ¦¡¤Æ¤§ÃC¦â¥N½X,¦p¦³±z¤è«Kªº¸Ü,¦A·Ð½Ðª©¥D±z¦AÀ°¦£¤@¤U,·P®¦!


[attach]13583[/attach]


[attach]13584[/attach]

TOP

¦^´_ 14# cmo140497
«D±`©êºp¡A§Ú¹ê¦b¬Ý¤£À´§A¾ãÅé¬yµ{»P©Ò»Ý®ÄªG
½Ð±Ð¥H¤U°ÝÃD
1¡B¤å¦rÀɪº¥Î·N¬O¬Æ»ò?
2¡B­nÅܦ⪺¦ì¸m¨ì©³¬O­þ­Ó¦ì¸m?
3¡B­nÅܦ⪺¦ì¸mªº¸ê®Æ¬O¦p¦ó¨ú±o?
4¡B½ÐºÉ¥i¯à±N±zªº©Ò¦³°Ê§@¬yµ{±Ô­z²M·¡
­n¥ÎÀɮ׸ÑÄÀ±zªº°ÝÃD¡A½Ð¥Î¤@­PªºÀÉ®×
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 15# Hsieh


    Dear Hsieh ª©¥D :
    ¹ê¦b¤£¦n·N«ä,³y¦¨±zªº§xÂZ,§Æ±æ±z¥i¨£½Ì..Ãö©ó±zªººÃ°Ý
1¡B¤å¦rÀɪº¥Î·N¬O¬Æ»ò?
       ³o¤å¦rÀÉ¥Nªí¤@­Ó²£«~ªºÀˬdµ²ªG,¤p§Ì·Qª¾¹D¥¦»P¨ä¥¦¶¡¦³¨S¦³¶°¤¤©Î­«Å|ªºÁͶÕ,ÂÇ¥H¤F¸Ñ¨Ã¸Õ§ä¥X¥i¯à¬O­þ¤@­Ó¬yµ{¥X°ÝÃD
2¡B­nÅܦ⪺¦ì¸m¨ì©³¬O­þ­Ó¦ì¸m?
      §Æ±æ¬O¥i¥H¦b©T©w¦ì¸mRange("AI5:BF28"),¦Ó¤£¬O¨C­Ó¤å¦rÀɪº¦ì¸m,¤£¦n·N«ä,¤p§Ì¨S¦³»¡©ú²M·¡,©êºp
3¡B­nÅܦ⪺¦ì¸mªº¸ê®Æ¬O¦p¦ó¨ú±o?
      ©Ò¥H¤p§ÌÅܧó¬yµ{,¤T­ÓID¬Û¦P¦ì§}¥u­n¦³<>0,«h­pºâ1,¤T­Ó³£<>0,«h­pºâ3,±N3°£¥H®Ö¨ú¤è¶ô¤Ä¿ïªº¤ù¼Æ3=100%,«h¥H100%¤§ÃC¦â
      ¶ñ¤J­nÅܦ⪺¦ì¸m§Y¥i,¤ð»Ý¦A¥Î¤å¦rÀɤ§¸ê®Æ»P­nÅܦ⤧¸ê®Æ§@¤ñ¹ï¤F,¤£ª¾³o¼Ë¬O§_¥i¦æ?
4¡B½ÐºÉ¥i¯à±N±zªº©Ò¦³°Ê§@¬yµ{±Ô­z²M·¡
    ¤£¦n·N«ä,¦]¬°¤W¯Z¥Î¤½¥q¹q¸£,¦³ÂILag,¤W¶Ç¸ê®Æ¦³®É©Ç©Çªº,³y¦¨±zªº§xÂZ,¹ê¦b©êºp

§Æ±æª©¥D¦AÀ°¦£¬Ý¤@¤U,·P®¦,ÁÂÁÂ!


¦h²Õ¼Æ¤§¤¸¯À­«ÂЦ¸¼Æ¤ñ¸û¨Ã§@±ø¥ó®æ¦¡¤Æ_2012-12-18.zip (25.28 KB)

TOP

¦^´_ 16# cmo140497
¤£ª¾¹D²z¸Ñ¬O§_¥¿½T
±N¤å¦rÀÉ»Pµ{¦¡Àɦܩó¦P¤@¸ê®Æ§¨¸Õ¸Õ
RawData.rar (26.7 KB)
  1. Sub InputData()
  2. Dim Btn(), Mystr$, ARng As Range
  3. fd = ThisWorkbook.Path & "\"
  4. fs = Dir(fd & "*.txt")
  5. With ActiveSheet
  6. .CheckBoxes.Delete
  7. .Cells.Clear
  8. Do Until fs = ""
  9. Open fd & fs For Input As #1
  10.    Do While Not EOF(1)
  11.      Line Input #1, Mystr
  12.      If InStr(Mystr, ":") > 0 Then
  13.      r = r + 1
  14.        .Cells(r, 3) = Split(Mystr, ":")(0)
  15.         If InStr(Split(Mystr, ":")(1), " ") > 0 Then
  16.            ar = Split(Split(Mystr, ":")(1), " ")
  17.            .Cells(r, 5).Resize(, UBound(ar) + 1) = ar
  18.         Else
  19.            .Cells(r, 5) = Replace(Split(Mystr, ":")(1), "ITEM", "")
  20.            ReDim Preserve Btn(s)
  21.            Btn(s) = Replace(Split(Mystr, ":")(1), "ITEM", "")
  22.            s = s + 1
  23.         End If
  24.      End If
  25.     Loop
  26. Close #1
  27. r = r + 1
  28.     fs = Dir
  29. Loop
  30. .Cells(r, 5).Resize(, UBound(ar) + 1).EntireColumn.AutoFit
  31. For i = 0 To s - 1
  32.   With .CheckBoxes.Add(.Cells(i + 4, "A").Left, .Cells(i + 4, "A").Top, .Cells(i + 4, "A").Width, .Cells(i + 4, "A").Height)
  33.      .Characters.Text = Btn(i)
  34.      .OnAction = "Get_Rng"
  35.   End With
  36. Next
  37. .Range(.Range(.[E2], .[E2].End(xlDown)), .Range(.[E2], .[E2].End(xlDown)).End(xlToRight)).Copy .[AI2]
  38. .[AI2].CurrentRegion.EntireColumn.AutoFit
  39. Set ARng = .[AI2].CurrentRegion
  40. ARng.Replace "___", ""
  41. ARng.SpecialCells(xlCellTypeConstants).Value = 0
  42. ARng.SpecialCells(xlCellTypeBlanks).Value = "___"
  43. End With
  44. ActiveWindow.Zoom = 75
  45. End Sub
  46. Sub Get_Rng()
  47. Dim A As Range, Rng As Range, Sp As Shape, CRng As Range
  48. With ActiveSheet
  49. For Each Sp In .Shapes
  50. If Sp.Name Like "Check Box*" Then
  51. If Sp.OLEFormat.Object.Value = 1 Then
  52. n = Sp.OLEFormat.Object.Caption
  53. Set A = .Columns("E").Find(n, lookat:=xlWhole)
  54. If Rng Is Nothing Then
  55.     Set Rng = A.CurrentRegion
  56.     Else
  57.     Set Rng = Union(Rng, A.CurrentRegion)
  58. End If
  59. End If
  60. End If
  61. Next
  62. If Rng Is Nothing Then
  63. MsgBox "Nothing"
  64. Else
  65. For x = 1 To Rng.Areas(1).Columns.Count
  66.    For y = 2 To Rng.Areas(1).Rows.Count
  67.    ReDim ay(1 To Rng.Areas.Count)
  68.    ReDim ary(1 To Rng.Areas.Count)
  69.       For i = 1 To Rng.Areas.Count
  70.       If Rng.Areas(i).Cells(y, x) = "000" Then .[AI2].CurrentRegion.Cells(y - 1, x).Interior.ColorIndex = 4: GoTo 10
  71.          ay(i) = Rng.Areas(i).Cells(y, x)
  72.       Next
  73.       For j = 1 To UBound(ay)
  74.          For s = 1 To UBound(ay)
  75.            If ay(j) = ay(s) Then cnt = cnt + 1
  76.          Next
  77.          ary(j) = cnt: cnt = 0
  78.       Next
  79.       g = Application.Lookup(Application.Max(ary) / Rng.Areas.Count, Array(0, 0.19, 0.39, 0.59, 0.79, 0.99, 1), Array(4, 44, 8, 6, 7, 3, 16))
  80.       With .[AI2].CurrentRegion.Cells(y - 1, x)
  81.       If .Value = "___" Then
  82.          .Interior.ColorIndex = -4142
  83.          Else
  84.          .Interior.ColorIndex = g
  85.        End If
  86.        End With
  87. 10
  88.     Next
  89. Next
  90. End If
  91. End With
  92. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

(¤w¸Ñ¨M)¦p¦ó¨Ï¥ÎVBA©Î¨ç¼Æ°µ¦h­Ó°}¦C¼Æ­Èªº¤ñ¸û¤Î®æ¦¡¤Æ±ø¥óªº³]©w

¦^´_ 17# Hsieh

·PÁª©¥D¤£§[«ü±Ð,³o¼Ë¬Ý°_¨Ó¯uªºÂ²³æ¦h¤F,¹ï©ó±zªº¤j®¦¤j¼w,¤p§Ì²¦¥ÍÃø§Ñ,¤Ó·PÁ±z¤F,ÁÂÁÂ!

TOP

¦^´_ 17# Hsieh


    Dear Hsiehª©¥D :
    ¤£¦n·N«ä,¦A«×¥´ÂZ±z¤@¤U,·PÁª©¥D±z´£¨Ñ¨ó§U,¤p§Ì´ú¸Õ¤F¤@¤U,¦³ÂI¤p¤pªºbug,¤£ª¾±z¬O§_¥i¥H¦AÀ°¤p§Ìdebug¤@¤U,¥D­n¦b¬Û°£¥À¼Æ,¦ü¥G§â¥k¦C¶È±ýÅã¥Üªº¹Ï­È,¥[¶i¨Ó¤F,Åܦ¨¥ô¦ó­È»P0¤ñ¸û,§¡Åܬ°0,·P®¦


TOP

ÁöµLÅv­­¤U¸ü...¤´ÁÂÁ±zªº¤À¨É...
§V¤O¼W¥[µ¥¯Å¤¤...

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD