[µo°Ý] ·Q±N­«¶q·h¨ì¬Û¦P¸ê®Æªº²Ä¤@¦æ

joslau

¤j®a¦n!

§Ú·Q±N¥ª¤èºñ¦âªº­«¶q
·h¨ì©M¥k¤è¸ê®Æ¤@¼Ë(E1:G11)
§Y¬O±N
C5¸ê®Æ·h¨ìC2
(§Y¬OB¦C¤¤1234567894ªº²Ä¤@¦æ)

C11·h¨ìC6
(§Y¬OB¦C¤¤1234567900ªº²Ä¤@¦æ)

§Ú¥ÎIF CELLS(I , 2)  = 1¤w¥i±NC5¸ê®ÆCOPY
¦ý§Ú¤£À´¦V¤WÀˬd¬Û¦P¸ê®Æ¤¤ªº²Ä¤@¦æ¦ì¸m

¥ýÁÂ

Book1.zip (5.21 KB)

¤U¸ü¦¸¼Æ: 12

2013-11-23 11:30

joslau

¦^´_  joslau
Hsieh µoªí©ó 2013-12-3 14:15

    «¢«¢~~~ÁÂÁÂHsieh¦^ÂÐ
....­n¦A¬ã¨s

(¨ä¹êGBKEE¨º¬qCODE¤wªá¤F¤£¤Ö®É¶¡¬ã¨s...)

Hsieh

¦^´_ 9# joslau

1. Sub ex()
   
2. Dim A As Range, B As Range, Rng As Range
   
3. With Sheet1
   
4. Set Rng = .Range("A:A").Find("DATA1", lookat:=xlWhole) '§äAÄæ¼ÐÃD¦C¦ì¸m
   
5. If Not Rng Is Nothing Then
   
6. If Application.Count(Rng.CurrentRegion.Columns(3)) > 0 Then '¸ê®ÆCÄæ¼Æ­È¼Æ¶q§PÂ_
   
7.    For Each A In Rng.CurrentRegion.Columns(3).SpecialCells(xlCellTypeConstants, 1) '¨C­Ó¼Æ­È°µ°j°é
   
8.      Set B = Rng.CurrentRegion.Columns(2).Find(A.Offset(, -2), lookat:=xlWhole) '§ä¨ìBÄæ²Ä¤@­Ó¦ì¸m
   
9.      If Not B Is Nothing Then A.Cut B.Offset(, 1) '²¾°Ê¦ì¸m
   
10.    Next
    
11. End If
    
12. End If
    
13. End With
    
14. End Sub

½Æ»s¥N½X

joslau

¤j­P§¹¦¨§Ú»Ý­nªºªF¦è¤F
¦A¦¸¦hÁÂGBKEE

1. Sub autopl()
   
2. Dim i As Long
   
3. Dim MyString, myLen
   
4. 
   
5. For i = 15 To [a65536].End(4).Row
   
6. MyString = Cells(i, 2)
   
7. If Len(MyString) = 10 Then
   
8.         Cells(i, 9).FormulaR1C1 = ""
   
9.         Cells(i, 10).FormulaR1C1 = ""
   
10.         Cells(i, 11).FormulaR1C1 = ""
    
11. End If
    
12. 
    
13. 
    
14. 
    
15. 
    
16. Next i
    
17. 
    
18. Ex
    
19. Ex2
    
20. Ex3
    
21. Ex4
    
22. add1
    
23. ctn1
    
24. 
    
25. 
    
26. End Sub
    
27. 
    
28. Sub Ex()
    
29.     Dim Rng As Range, E As Range, lastrow3 As Long
    
30.     With ActiveSheet
    
31.     lastrow3 = Worksheets("sheet1").Range("a15").End(xlDown).Row
    
32.         Set Rng = .Range("J15:J" & lastrow3&).SpecialCells(xlCellTypeBlanks)
    
33.         For Each E In Rng.Areas
    
34.             If E.Cells(E.Rows.Count).Row < .Rows.Count Then
    
35.                 E.Cells(1) = E.Cells(E.Rows.Count + 1)
    
36.                 E.Cells(E.Rows.Count + 1) = ""
    
37.             End If
    
38.         Next
    
39.     End With
    
40. End Sub
    
41. 
    
42. Sub Ex2()
    
43.     Dim Rng2 As Range, D As Range, lastrow2 As Long
    
44.     With ActiveSheet
    
45.     lastrow2 = Worksheets("sheet1").Range("a15").End(xlDown).Row
    
46.         Set Rng2 = .Range("I15:I" & lastrow2&).SpecialCells(xlCellTypeBlanks)
    
47.         For Each D In Rng2.Areas
    
48.             If D.Cells(D.Rows.Count).Row < .Rows.Count Then
    
49.                 D.Cells(1) = D.Cells(D.Rows.Count + 1)
    
50.                 D.Cells(D.Rows.Count + 1) = ""
    
51.             End If
    
52.         Next
    
53.     End With
    
54. End Sub
    
55. 
    
56. Sub Ex3()
    
57.     Dim Rng3 As Range, f As Range, lastrow5 As Long
    
58.     With ActiveSheet
    
59.     lastrow5 = Worksheets("sheet1").Range("a15").End(xlDown).Row
    
60.         Set Rng3 = .Range("k15:k" & lastrow5&).SpecialCells(xlCellTypeBlanks)
    
61.         For Each f In Rng3.Areas
    
62.             If f.Cells(f.Rows.Count).Row < .Rows.Count Then
    
63.                 f.Cells(1) = f.Cells(f.Rows.Count + 1)
    
64.                 f.Cells(f.Rows.Count + 1) = ""
    
65.             End If
    
66.         Next
    
67.     End With
    
68. End Sub
    
69. 
    
70. Sub Ex4()
    
71. 
    
72. Dim rw As Long
    
73. For rw = 15 To [a65536].End(4).Row
    
74. If Cells(rw, 2).Value = 1 Then Cells(rw, 2).EntireRow.Delete
    
75. Next rw
    
76. 
    
77. End Sub
    
78. 
    
79. Sub add1()
    
80. 
    
81. Dim l As Long
    
82. 
    
83. For l = 15 To [a65536].End(4).Row
    
84. If Cells(l, 2) <> "" Then
    
85.         Cells(l, 2).FormulaR1C1 = ""
    
86. End If
    
87. Next l
    
88.    
    
89. 
    
90. End Sub
    
91. 
    
92. Sub ctn1()
    
93. Dim lastrow4 As Long
    
94. 
    
95.     With ActiveSheet
    
96.     lastrow4 = Worksheets("sheet1").Range("a15").End(xlDown).Row
    
97.     Range("b15").FormulaR1C1 = "0"
    
98.     Range("b16").FormulaR1C1 = "=IF(RC[7]="""",R[-1]C,R[-1]C+1)"
    
99.     Range("b16").AutoFill Destination:=Range("b16:b" & lastrow4)
    
100.     End With
     
101. End Sub

½Æ»s¥N½X

joslau

§¹¦¨!!!!!!

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, E As Range, lastrow As Long
   
4.     With ActiveSheet
   
5.     lastrow = Worksheets("sheet1").Range("a12").End(xlDown).Row
   
6.         Set Rng = .Range("a12:L" & lastrow&).SpecialCells(xlCellTypeBlanks)
   
7.         For Each E In Rng.Areas
   
8.             If E.Cells(E.Rows.Count).Row < .Rows.Count Then
   
9.                 E.Cells(1) = E.Cells(E.Rows.Count + 1)
   
10.                 E.Cells(E.Rows.Count + 1) = ""
    
11.             End If
    
12.         Next
    
13.     End With
    
14. End Sub

½Æ»s¥N½X

joslau

¦^´_ 5# GBKEE

ÁÂÁ¦^ÂÐ

    ¦]¨C¦¸¦æ¼Æ¤£¤£¼Ë....¥¿¬ã¨s¤¤

c_c_lai

¦^´_ 5# bmouth
4# ¥Î·Æ¹«¶K»s§Y¥i¡C

GBKEE

¦^´_ 5# bmouth
§A¥i»s§@ 3# ¹Ï¥Ü¤º®e,°õ¦æ4#ªºµ{¦¡½X,¸Õ¸Õ

GBKEE

¦^´_ 3# joslau

1. Sub Ex()
   
2.     Dim Rng As Range, E As Range
   
3.     With ActiveSheet
   
4.         Set Rng = .Range("C12:C23").SpecialCells(xlCellTypeBlanks)
   
5.         For Each E In Rng.Areas
   
6.             If E.Cells(E.Rows.Count).Row < 23 Then
   
7.                 E.Cells(1) = E.Cells(E.Rows.Count + 1)
   
8.                 E.Cells(E.Rows.Count + 1) = ""
   
9.             End If
   
10.         Next
    
11.     End With
    
12. End Sub

½Æ»s¥N½X

joslau

¦^´_  joslau
¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2013-11-23 14:13

ÁÂÁ¦^ÂÐ

¦ý¦pªG§Ú¥HA¦C°µ¤@­Ó½d³ò
¦Ó¸Ó½d³ò¬O¥HA12¶}©l

½d³ò¥~ªº¤W¤U¹j¤F¤@¦æ¥i¯à·|¦³¨ä¥L¸ê®Æ

¤U¸ü (15.11 KB)

2013-11-23 17:38

§Y¬O¬õ¦â³¡¥÷¥i¯à·|¦³¨ä¥L¸ê®Æ

¬O§ï" Set Rng = "¨º¦æ?

1. Worksheets("Sheet1").Range("c12").End(xlDown).SpecialCells(xlCellTypeBlanks)

½Æ»s¥N½X

¦n¹³°_¤£¤F§@¥Î

À³¸Ó¬O«ç¼Ë¼g?