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

[µo°Ý] ¦p¦ó§Ö³t½Æ»s³Ñ¤U¨S³Q¿ï¦±ªº?

°ÝÃD¤@¡G
¦pªG­n½Æ»s¨ì¥kÃä¤S­n½Æ»s¨ìSheet3¨âºØ³£­n¦³¸Ó¦p¦ó°µ©O?

¤£¬O¨â­Óµ{§Ç¦X¨Ö°_¨Ó´N¦n¤F¶Ü?
¡£°õ¦æ¡¤½Æ»s¨ì¥kÃä
¡£°õ¦æ¡¤½Æ»s¨ìSheet3
°ÝÃD¤G(¶i¶¥ÃD)¡G
§â¥´¤Äªº¦a¤è§ï¦¨
­ì¥»A,B,C,D,¥´¤Ä
§ï¦¨A,B,¥´¤Ä,C,D¦ì¸m§ó°Ê¡A

C,D¦ì¸m§ó°Ê  ­n§ï¨ì¨º¸Ì?

TOP

¥»©«³Ì«á¥Ñ av8d ©ó 2014-3-15 21:09 ½s¿è
  1. Private Sub CommandButton1_Click()
  2.     Dim Rng, rngE As Range, row1, i As Integer
  3.    
  4.     Dim sh1, sh3 As Object
  5. '   Set sh1 = ThisWorkbook.Sheets("Sheet1")
  6.     Set sh3 = ThisWorkbook.Sheets("¤u§@ªí3")
  7.    
  8.     end1 = [A65536].End(xlUp).Row
  9.     Set rngE = [E2].Resize(end1, 1)
  10.     i = 2
  11.     For Each Rng In rngE
  12.         If Rng = "v" Then
  13.             Cells(Rng.Row, 1).Resize(1, 4).Copy
  14.             row1 = [I65536].End(xlUp).Offset(1, 0).Row
  15.             Cells(row1, 9).Resize(1, 4).Select
  16.             ActiveSheet.Paste
  17.         End If
  18.         sh3.Activate
  19.         If Rng = "v" Then
  20.             Cells(Rng.Row, 1).Resize(1, 4).Copy
  21.             sh3.Cells(i, 2).Select
  22.             ActiveSheet.Paste
  23.             i = i + 1
  24.         End If
  25.     Next
  26. End Sub
½Æ»s¥N½X
µ²¦X«áÅܦ¨³o¼Ë?¦ý¬O·|¥X²{¿ù»~

    §ó°Ê¨ì®ÇÃä D E
C¯dµ¹¥´¤Äªº

TOP

§ó°Ê¨ì®ÇÃä D E, C¯dµ¹¥´¤Äªº
¥»¨Ó ÄæE ¥Î¨Ó¥´¤Ä,
´¡¤J ÄæC, Åܦ¨ ÄæC ¥Î¨Ó¥´¤Ä, ¨ä¾l¦V¥k²¾?

§ïÅÜ Worksheet_SelectionChange ªº IJ°Ê¹ï¹³ ´N¥i¥H¤F,
¸Ô¨£ 4f¤Î6f ªº»¡©ú

¤]´N¬O±N
    Set rngE = [E1].Resize(end1, 1)
§ï¦¨
    Set rngE = [C1].Resize(end1, 1)
´N¥i¥H¤F

TOP

¦^´_ 10# av8d
play.gif
  1. Private Sub CommandButton1_Click() '¤Ï¿ï
  2. Application.EnableEvents = False
  3. Dim Rng As Range
  4. For Each a In Range("C:C").SpecialCells(xlCellTypeBlanks)
  5. If Rng Is Nothing Then
  6.   Set Rng = Union(Cells(a.Row, "B"), Cells(a.Row, "D"))
  7.   Else
  8.   Set Rng = Union(Rng, Union(Cells(a.Row, "B"), Cells(a.Row, "D")))
  9. End If
  10. Next
  11. Rng.Copy [J1]
  12. Rng.Copy Sheets(3).[B2]
  13. Application.EnableEvents = True
  14. End Sub

  15. Private Sub CommandButton2_Click() '¿ï¨ú
  16. Application.EnableEvents = False
  17. Dim Rng As Range
  18. Set Rng = Union([B1], [D1])
  19. For Each a In Range("C:C").SpecialCells(xlCellTypeConstants)
  20.   Set Rng = Union(Rng, Union(Cells(a.Row, "B"), Cells(a.Row, "D")))
  21. Next
  22. Range("J1").CurrentRegion.Clear
  23. Rng.Copy [J1]
  24. Sheets(3).[B2].CurrentRegion.Clear
  25. Rng.Copy Sheets(3).[B2]
  26. Application.EnableEvents = True

  27. End Sub

  28. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '¥´¤Ä
  29. If Cells(Target.Row, 1) <> "" Then Cells(Target.Row, 3) = IIf(Cells(Target.Row, 3) = "", "v", "")
  30. End Sub
½Æ»s¥N½X
¤Ï¿ï.zip (21.77 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ av8d ©ó 2014-3-17 19:40 ½s¿è

¦^´_ 14# Hsieh

§Ú¹Á¸Õ­×§ï¤F¤@¤Uµ{¦¡~¥i¯à¬O§ÚÁÙ¨S²z¸Ñ~
©Ò¥HÁÙµLªk§ï¥X§Ú§Æ±æªºµª®×~¯à§_¦³³Ò¤j¤j¥N¬°­×§ï¤F
    §Æ±æµª®×¬O³o¼Ë~¦p¤U
¥¿¤Ï.rar (20.51 KB)

IJµo¥´¤Äªº¥u­nC,DÄæ´N¦n~¨ä¾lªºÄæ¦ì§Æ±æ¥i¥H¤£­nIJµo~ÁÂÁÂ

TOP

¦^´_ 13# yen956


    ½Ð°Ý¯à§_¥u«ö¤U¤@­Ó«ö¶s¥i¥H¦P®É§¹¦¨?¦p¤U
³æ«ö¶s.rar (21.41 KB)

¦]¬°¤u§@ªí2ªº¤j¤ò©M¤T¤ò³Q¥´¤Ä
«ö¤U«ö¶s«á~²£¥Í¤FJ1:M3
¦P®É¤]²£¥Í¤F¤u§@ªí1ªºB2:D3

¥i¯à¦³ÂIÃø«×~¤£¹LÁÙ¬O«Ü·PÁ¤j¤jªºÀ°¦£

TOP

¦^´_ 15# av8d
Private Sub Worksheet_SelectionChange(ByVal Target As Range) '¥´¤Ä
If Cells(Target.Row, 1) <> "" And Target.Column = 3 Or Target.Column = 4 Then Cells(Target.Row, 3) = IIf(Cells(Target.Row, 3) = "", "v", "")
End Sub
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 17# Hsieh


    «D±`·PÁÂHsieh¤j¤j©Myen956¤j¤jªº¤j¤OÀ°§U
¨ü¯q¨}¦h~Åý§Úªº°ÝÃD³£ªï¤b¦Ó¸Ñ~¦A¦¸·PÁ¡I

TOP

¦^´_ 17# Hsieh
ÁÙ¦n¦³¤j¤jªºÀ°¦£, ¤£µM¯u·|³Q¦Ò­Ë, ¦¬¤U°Õ, ÁÂÁÂ!!

TOP

¥»©«³Ì«á¥Ñ av8d ©ó 2014-3-21 11:12 ½s¿è

¦^´_ 17# Hsieh


    ½Ð±ÐH¦Ñ®v~
§Ú±N±zªºµy·L§ï¤F¤@¤U
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '¥´¤Ä
  2. If Cells(Target.Row, 1) <> "" And Target.Column = 3 Or Target.Column = 5 Then Cells(Target.Row, 5) = IIf(Cells(Target.Row, 5) = "", "v", "")
  3. End Sub
½Æ»s¥N½X
½Ð°Ý§Ú»s§@¤F¤@­Ó«ö¶s~·QÅý¥L«ö¤U¥h«á~¥þ¿ï©Î¥þ§R
¬O§_¦³¿ìªk?¦A¦¸·PÁÂ

-------------------------------------------------------------------
¥þ§R§Ú¤j·§·Q¨ì¤F~´N¬Oª½±µ«ü©w§R°£¤@¾ã¦æ~
¥þ¿ï¤j·§·Q¨ìªº¤èªk´N¬O­t¨î¨S¥´¤Äªº
¦A¦¸·PÁÂH¦Ñ®v

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD