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

[µo°Ý] ¦p¦ó§PÂ_Àx¦s®æ¤¤¦³«_¸¹¡H

[µo°Ý] ¦p¦ó§PÂ_Àx¦s®æ¤¤¦³«_¸¹¡H

§PÂ_Àx¦s®æ¬O§_¦³«_¸¹.rar (6.49 KB)

¸Ô¦pªþ¥ó¡A¦bBÄæ¤â°Ê²£¥Í«á¡AA¡BCÄæ¦Û°Ê²£¥Í(ºc«ä¦p¤U)

For i = 2 to
  if cells(i,2) ¦³«_¸¹ Then
    cells(i,1) = ROW()-1
    Na = cells(i,2)
  else
    cells(i,3) = Na
Next

¦ý¬O¬d§ä¤Fºô¸ô¥u¦³§ä¨ìSet cell = Range("B1:B2000").FIND(":", LookIn:=xlValues, LookAt:=xlPart)
®M¥Î¨ì°j°é¤£ª¾¬O§_¦³Ãþ¦üªº¼gªk¡HÁٽЫe½ú­Ì§iª¾¡A·P¿E¤£ºÉ¡I

¦^´_ 6# hcm19522

hcm19522«e½ú±z¦n¡A¬Ý¤F±zªº¨ç¼Æ¨ü¯q¨}¦h¡A
¦³­Ó¶i¶¥°ÝÃD¡A¸Ô¦pªþ¥ó¡A½Ð°Ý½s¸¹¯à§_¤]¯à¦Û°Ê½s½X©O¡H
«_¸¹±q1¶}©l½s¡A©³¤Uªº1-1¡B1-2....²Ä¤G­Ó«_¸¹±q2¶}©l½s¡A©³¤Uªº2-1¡B2-2....¥H¦¹Ãþ±À

§PÂ_Àx¦s®æ¬O§_¦³«_¸¹_2.rar (24.93 KB)

TOP

·PÁÂsingo1232001¡Bikboy¡B­ã´£³¡ªL¡Bhcm19522¡A
µL½×¬O«e½ú©Îª©¥D¡A´£¨ÑªºVBA©Î¨ç¼Æ¼gªk³£¨ü¯q¨}¦h¡A
¥Ñ°J·PÁ¡A¤]¯¬¤j®a·s¬K¨Î¸`´r§Ö¡C

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

Sub TEST_A1()
Dim Arr, i&, T$, TT$, SS$
Arr = Range([a1], [b65536].End(xlUp))
For i = 2 To UBound(Arr)
    T = Arr(i, 2): SS = TT
    If Right(T, 1) = ":" Then TT = Replace(T, ":", ""): SS = ""
    Arr(i - 1, 1) = SS
Next i
[c2].Resize(UBound(Arr) - 1) = Arr
End Sub

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2022-1-29 23:21 ½s¿è

¦^´_ 1# av8d


¤º¦³3¥÷ÀÉ®×  ³£¦³­×§ï¹L
¤j­P»¡©ú¤@¤U©Ò¦³±±¨î
1. ¶}ÀÉ´N·|IJµo¤£·|¥X¿ù
2. ½d³ò¿ï¨ú§R°£¤£·|¥X¿ù
3. [a1:d1] ¦Û°Ê·|¼W¥[¼ÐÅÒ
4. [a2] ·|¦Û°Ê©ñ1  AÄæ§Ç¸¹·|¦Û°Ê¼W¥[
5.  CÄæ ·|¦Û°Ê·j´M¤W¤@­Ó³Ìªñªº«_¸¹¦WºÙ

(v1)  
1.  ¦³«_¸¹ªº¥kÃäCÄæ ·|±j¨î§e²{ªÅ¥Õ

(v2)·|¤ñ¸û¯S§O¤@ÂI
1.   ¦³«_¸¹ªº¥kÃäCÄæ ·|±j¨î§e²{ªÅ¥Õ
2.   bÄæ­Y ¤¤¶¡¦³ªÅ¥Õ¦ì¸ê®ÆÂ_µõ ´N¤£Ä²µoªº¥\¯à
3.   bÄæ ¿ï¾Ü®Ø­Y¸g¹L¦³ ¦Û°Ê§ó·s¥\¯à ³q±`¬O©È¦³¤W­±¸ê®Æ»Ý­n­×§ï

(v3)  
1.  ¦³«_¸¹ªº¥kÃäCÄæ ·|±j¨î§e²{ªÅ¥Õ
2.  bÄæ­Y ¤¤¶¡¦³ªÅ¥Õ¦ì¸ê®ÆÂ_µõ  ¤@¼Ë·|IJµoªº¥\¯à
3.  bÄæ ¿ï¾Ü®Ø­Y¸g¹L¦³ ¦Û°Ê§ó·s¥\¯à ³q±`¬O©È¦³¤W­±¸ê®Æ»Ý­n­×§ï

³Ì«á ¤£¤@©w¯à¦b¨Ï¥Î¤W§¹¥þ­è¦nº¡¨¬±zªº»Ý¨D
³o¨â¥÷Àɮ׳»¦h¬OºÉ¶q®i¥Ü¥\¯à
­Y¦³¥\¯à·Q­n´£¤É ©Î®ø°£ ¦A½Ð¦^³ø

§PÂ_Àx¦s®æ¬O§_¦³«_¸¹.zip (66.57 KB)

TOP

  1. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  2. Dim i&, j&
  3. If Not Target.Column = 2 Or Len(Target.Value) = 0 Then Exit Sub
  4. i = Target.Row
  5. If i < 2 Or Right(Target.Value, 1) = ":" Then Exit Sub
  6. Cells(i, 1) = Application.Max(Range("a1:a" & i)) + 1
  7. j = i
  8. Do
  9.     j = j - 1
  10. Loop Until Right(Cells(j, 2), 1) = ":"
  11. Cells(i, 3).Value = Left(Cells(j, 2).Value, Len(Cells(j, 2).Value) - 1)
  12. End Sub
½Æ»s¥N½X

zz.zip (13.21 KB)

TOP

¦^´_ 1# av8d


¥i¯à¦³bug À°¦£´ú¬Ý¬Ý

§PÂ_Àx¦s®æ¬O§_¦³«_¸¹.zip (15.96 KB)

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD