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

[µo°Ý] ½Ð±Ð­n¦p¦ó¨Ï¥Î VBA §P§O¤£³W«hÀx¦s®æªº¿ìªk2

¦^´_ 9# c_c_lai
.Parent ¬O«ü¨ì «e­± With Sheets(1).[A1].CurrentRegion ªº parent ¤]´N¬O sheets(1)
ª½±µ¥Î Range(prev) ¡AÀ³¸Ó¬Û·í©óActiveSheet.Range(prev)¡A·Ó¹D²z¬O¨SÔ£¼vÅT¡C

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2013-12-6 12:31 ½s¿è

¦^´_ 1# c_c_lai

½Ð°ÝÀɮ׮פ¤¦³¹Ï¤ù¡A¦p¦ó±N¹Ï¤ùªº¹Ï§ÎÂন 0, 255¼Æ¦r

ºâ0ªº­±¿n¡A§Úªì¨Bºâ§¹¦³381²Õ(¤]¤£ª¾¹D¹ï¤£¹ïÁÙ¨S¦³Åçºâ)¡A¨ä¤¤¦³«Ü¦h²Õ¬O 0¡A»Ý­n§â0²Õ¹LÂo±¼¶Ü?
¦³¼Ð·Çµª®×¥i¥Hµ¹§ÚÅçÃÒ¶ý?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# c_c_lai
¸ê®Æ¶qÃe¤j¡A¥B¦]¬°¦U­ÓÀx¦s®æ¥²¶·­n¥H»¼°j¤è¦¡´ú¬O³sÄò»P§_
¶]ªº¦³ÂI¤[
¥ÎÁY¤p¸ê®Æ¶q¥ý¦æ´ú¸ÕÀ³¸ÓÁÙOK¡A§A´ú¸Õ¬Ý¬Ý
­±¿n¤j¤p¤À§G²Î­p.zip (112.96 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 12# ML089
¤£¦n·N«ä²{¦b¤~¦^¨Ó¡I
¨º±i¹Ï¤ù¥u¬O¼Ë±i¦Ó¤w¡A
§Ú­Ó¤H¥ç¦b±´¯Á¤¤¡A¨º¬O§Ú¥t¤@¤Í¤Hªº´£°Ý¡A
¤]¦h»X¦U¦ì¹©¤O¨ó§U¡A§Æ±æ¯à±q¤¤§ä¥XÀYºü¡C

TOP

¦^´_ 13# Hsieh
ÁÂÁ±z¡I
«Ý´ú¥Xµ²ªG«á¦A¦æ¦V±z³ø§i¡C

TOP

¦^´_ 14# c_c_lai

½Ð°Ý³s¦b¤@°_±o­ì«h¬O¤°»ò¡A½Ð¬Ý¹Ï¤¤»¡©ú

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 13# Hsieh
¦^´_ 16# ML089
¦^´_ 11# stillfish00
ÁÂÁ¤j®a¡A²×©ó´ú¸Õ¥Xµ²ªG¤F¡AÁöµM³t«×¤WµyµyºC¤FÂI¡G

TOP

¦^´_ 13# Hsieh
¦^´_ 16# ML089
¦^´_ 11# stillfish00
­±¿n¤j¤p¤À§G²Î­pB.rar (108.71 KB)
·PÁ¦U¦ìªºÀ°¦£¡A¦pÁÙ¦³§ó¨Î®Ä¯qªº¸Ñ¨M¤è®×¡A
·q½Ð¤£§[«ü±Ð¡I

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-12-7 08:05 ½s¿è

¦^´_ 16# ML089
¥H±`²z¨Ó½×¡A 10 »P 25 À³µL©¼¦¹¶¡¤§¦êÁp¡AÀ³Äݤ£¦P¤§°Ï¶ô¤~¬O¡A
§A¦b¹ê§@¤W¬O¦p¦ó¸ÑªRªº¡H

TOP

¦^´_ 16# ML089
Hsieh ª©¤j¥L¬OÀ³¥Î "»¼°j" ªº³B²z§Þ¥©¡A¨Ó¨D¥X¦UÂkÄݰ϶ôªº½d³ò¡A
³oºØÂI¤Î©ó­±ªº¹B¥Î¬O­ÓÆZ¤£¿ùªº Idea¡A§A¬Ý¬Ýµ{¦¡½X²Ó²Ó¦a¥h±ÀºV¡A
¬OÆZ­È±o¦^¨ýªº¡A ¦bùØÀY§Ú¥[¤W¤F sRng ªº¦ì§}¬ö¿ý³B²z¡A¨Ã¦P®É¨D¨ú
¨C­Ó°Ï¶ôªº¹ê»Ú¦ì§}¼Æ¡A¥H´£¨Ñ°Ñ¦Ò¤§¥Î¡G
  1. Option Explicit

  2. Public s As Long
  3. Public sRng As Variant

  4. Sub ex()
  5.     Dim dic As Object
  6.     Dim A As Range, Rng As Range, sPos As Range
  7.    
  8.     Set dic = CreateObject("Scripting.Dictionary")
  9.     dic("³sÄò¼Æ¶q") = "¼Æ¶q"
  10.     '   ¼W¦C³¡¤À
  11.     ¤u§@ªí2.[C1] = "³sÄò¦ì§}"
  12.     ¤u§@ªí2.[D1] = "²Õ¦X¼Æ¶q"
  13.    
  14.     With ¤u§@ªí1
  15.         Set Rng = .Range("A1").CurrentRegion
  16.         Rng.Replace 0, Empty, xlWhole
  17.         
  18.         Set sPos = ¤u§@ªí2.[C2]
  19.         Set A = Rng.Find(Empty)
  20.         
  21.         Do Until A Is Nothing
  22.             A = 0: s = 1
  23.             Cnt A
  24.             dic(s) = dic(s) + 1
  25.             
  26.             sPos = sRng
  27.             sPos.Offset(0, 1) = Range(sRng).Count
  28.             
  29.             Set A = Rng.Find(Empty)
  30.             
  31.             Set sPos = sPos.Offset(1)
  32.         Loop
  33.     End With
  34.    
  35.     With ¤u§@ªí2
  36.         .[A1].Resize(dic.Count, 1) = Application.Transpose(dic.keys)
  37.         .[B1].Resize(dic.Count, 1) = Application.Transpose(dic.items)
  38.         .[A1].Resize(dic.Count, 2).Sort key1:=.[A1], Header:=xlYes
  39.     End With
  40.    
  41.     '  MsgBox dic.Count - 1
  42.     Set dic = Nothing
  43. End Sub

  44. Function Cnt(Rng As Range)
  45.     Dim A As Range, Temp As Range
  46.     Dim i As Integer
  47.         
  48.     For Each A In Rng
  49.         For i = -1 To 1 Step 2
  50.             If A.Row + i > 0 And A.Row + i < ¤u§@ªí1.Range("A1").CurrentRegion.Rows.Count Then
  51.                 If IsEmpty(A.Offset(i, 0)) Then Set Temp = Union(Rng, A.Offset(i, 0))   '  "$BF$4:$BF$5"
  52.             End If
  53.         Next
  54.         For i = -1 To 1 Step 2
  55.             If A.Column + i > 0 And A.Column + i < ¤u§@ªí1.Range("A1").CurrentRegion.Columns.Count Then
  56.                 If IsEmpty(A.Offset(, i)) Then Set Temp = Union(Rng, A.Offset(, i))   '  "$BF$4:$BG$4"
  57.             End If
  58.         Next
  59.     Next

  60.     If Not Temp Is Nothing Then      '  True
  61.         Temp = 0
  62.         s = Temp.Count
  63.         sRng = Temp.Address
  64.         Cnt Temp
  65.     End If
  66. End Function
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD