GBKEE

¦^´_ 2# luhpro

1. Private Sub CommandButton2_Click()
   
2.         With Columns("A:C")
   
3.             .ClearContents
   
4.             'ClearContents ¤èªk ²M°£½d³òªº¤½¦¡¡C²M°£¹Ïªí¤¤ªº¸ê®Æ¦ý«O¯d®æ¦¡³]©w¡C
   
5.             '****²M°£½d³òªº¤½¦¡¤]¥]§t²M°£½d³òªº¦r¦ê***

½Æ»s¥N½X

¸Õ¸Õ¬Ý

1. 'Sheet1¼Ò²Õªºµ{¦¡½X
   
2. Option Explicit
   
3. Dim d As Object
   
4. Private Sub CommandButton1_Click()
   
5.     Dim Text(1 To 2) As String
   
6.     Text(1) = Trim(TextBox1)
   
7.     Text(2) = Trim(TextBox2)  '¼Ó¥DÀɮצ³¥Î¨ìTextBox2
   
8.     UsedRange.ClearFormats    '¨ú¥N  ²M³æCLEAR«ö¶s , CommandButton2
   
9.     If Text(1) <> "" And Text(2) <> "" Then
   
10.         If d Is Nothing Then item_qty
    
11.         If d.EXISTS(Text(1) & Text(2)) Then
    
12.             d(Text(1) & Text(2)).Interior.ColorIndex = 44
    
13.         End If
    
14.     End If
    
15. End Sub
    
16. Private Sub item_qty()
    
17.     Dim r As Range
    
18.     Set d = CreateObject("scripting.dictionary") '¦r¨åª«¥ó
    
19.     For Each r In UsedRange.Rows
    
20.         'item(AÄd)&qty(CÄd)¤º®e¬°¦r¨åª«¥ókey­È,«ü©w¨ì Range
    
21.         Set d(r.Cells(1, 1) & r.Cells(1, 3)) = r.Cells(1, 1).Resize(, 3)        
    
22.     Next
    
23. End Sub

½Æ»s¥N½X

¤£¥Î¨Ï¥Î CommandButton1,
TextBox1,TextBox2 ¦³§ïÅܪ½±µÅã¥Ü

1. 'Sheet1¼Ò²Õªºµ{¦¡½X
   
2. Option Explicit
   
3. Dim d As Object
   
4. Private Sub TextBox1_Change()
   
5.     Ex
   
6. End Sub
   
7. 
   
8. Private Sub TextBox2_Change()
   
9.     Ex
   
10. End Sub
    
11. Private Sub Ex()
    
12.     Dim Text(1 To 2) As String
    
13.     Text(1) = Trim(TextBox1)
    
14.     Text(2) = Trim(TextBox2)  '¼Ó¥DÀɮצ³¥Î¨ìTextBox2
    
15.     UsedRange.ClearFormats    '¨ú¥N  ²M³æCLEAR«ö¶s , CommandButton2
    
16.     If Text(1) <> "" And Text(2) <> "" Then
    
17.         If d Is Nothing Then item_qty
    
18.         If d.EXISTS(Text(1) & Text(2)) Then
    
19.             d(Text(1) & Text(2)).Interior.ColorIndex = 44
    
20.         End If
    
21.     End If
    
22. End Sub
    
23. Private Sub item_qty()
    
24.     Dim r As Range
    
25.     Set d = CreateObject("scripting.dictionary") '¦r¨åª«¥ó
    
26.     For Each r In UsedRange.Rows
    
27.         'item(AÄd)&qty(CÄd)¤º®e¬°¦r¨åª«¥ókey­È,«ü©w¨ì Range
    
28.         Set d(r.Cells(1, 1) & r.Cells(1, 3)) = r.Cells(1, 1).Resize(, 3)
    
29.         
    
30.     Next
    
31. End Sub

½Æ»s¥N½X

GBKEE

¥t, Range ªº Copy «ü¥O¨Ï¥Î®É,
­Y¥u¦³«ü©w Destination ¦ÓµL¨ä¥L¯S®í»Ý¨D,
«h Destination:= ¥i¥H¤£¥Î¦C¥X¨Ó,
¥u­n¥´ Cells(2, 8).Copy Cells(3, 8) §Y¥i.
luhpro µoªí©ó 2014/8/23 09:25

¬O¥i¤£«ü©ú²Ä¤@­Ó°Ñ¼Æªº¦WºÙ

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
¤£«ü©ú»Ý   , , , expression.Find( "TEST", , , , , , ,  ,  TRUE)
«ü©ú¥i¬Ù²¤ , , , expression.Find( "TEST",  SearchFormat:=True)