¦p¦ó§Q¥ÎVBA¤@Áä¦Û°Ê¶ñ¤J1©Î3©O?

RCRG

¤@Áä¦Û°Ê¶ñ¤J1©Î3.rar (11.86 KB)

¦p¦ó¤@Áä¦Û°Ê¶ñ¤J1©Î3
¤U¸ü¦¸¼Æ: 8

2015-11-12 09:05

¤U¸ü (23.7 KB)

2015-11-12 09:06

lpk187

1. Sub «ö¶s1_Click()
   
2. Dim Rng As Range
   
3. With Sheets("Sheet2")
   
4.     For Each Rng In .Range("C3:C79")
   
5.         If Rng <> "" Then
   
6.             Set da = .Range("AA3:AA500").Find(Rng.Value, LookAt:=xlWhole, SearchDirection:=2)
   
7.             If da Is Nothing Then
   
8.                 Rng.Offset(, -1) = 3
   
9.             Else
   
10.                 Rng.Offset(, -1) = 1
    
11.                 Set da = Nothing
    
12.             End If
    
13.         End If
    
14.     Next
    
15. End With
    
16. End Sub

½Æ»s¥N½X

¦^´_ 1# RCRG

­ã´£³¡ªL

Sub TEST()
With [B3:B79]
¡@¡@¡@.Formula = "=IF(C3="""","""",3^(1-COUNT(MATCH(C3,AA$2:AA$500,))))"
¡@¡@¡@.Value = .Value
End With
[D1] = Now
End Sub

RCRG

ÁÂÁ¨â¦ìªº¦^µª¡A´ú¸Õ¨â¦ìªºµ²ªG³£¬O§Ú­nªºµª®×¡A¯uªº«D±`ÁÂÁ¡I¦Û±qª¾ÃÑ+§ïª©«á¡A´£¥X°ÝÃD¦n¹³³£¥Û¨I¤j®ü¡A¯Á©ÊÅý§Úµo²{³o­Ó½×¾Â¡A§óÅý§ÚÅåÆvªº¬O¡A¥H«e´¿¸g¦bª©ª¾ÃÑ+À°§Ú¸Ñµª¹Lªºexcel±M®a­Ì³ºµM³£¦b³o¨à¡A¯uªº¦nÅý§Ú¶}¤ß¡I§Æ±æ¤é«áÁÙ¯àÀò±o¦U¦ì¤j¤j½ç±Ð»P«ü¾É¡C

RCRG

¦^´_ 3# ­ã´£³¡ªL


·Q½Ð°Ý¤@¤U­ã¤j¬Û¤Ï°µªk¡A­Y²Å¦X¸¹½X´N¶ñ3¡A§_«h´N¶ñ1¡A¨º¤½¦¡­n¦p¦ó­×§ï©O?

­ã´£³¡ªL

¦^´_ 5# RCRG


=IF(C3="","",3^COUNT(MATCH(C3,AA$2:AA$500,)))
=IF(C3="","",IF(COUNT(MATCH(C3,AA$2:AA$500,)),3,1))
=IF(C3="","",IF(ISNA(MATCH(C3,AA$2:AA$500,)),1,3))

RCRG

¦^´_ 6# ­ã´£³¡ªL


    ­ì¨Ó¦p¦¹¡A¤U­±¤T­Ó³£¥i¥H³q¥Î¬O§a¡I¥i¬O¦n¹³·|¥X²{¿ù»~¡A¦Û¤v¤ÏÂдú¸Õ«á¡AÀ³¸Ó¬O¤Ö¤F""¡A©Ò¥H¦p®e³\§Ú§â¥¿¸Ñ¦APO¤@¦¸§a¡I
   ³Ì«á¤]¦A¦¸ÁÂÁ ­ã¤j Åo¡I


=IF(C3="""","""",3^COUNT(MATCH(C3,AA$3:AA$500,)))
=IF(C3="""","""",IF(COUNT(MATCH(C3,AA$3:AA$500,)),3,1))
=IF(C3="""","""",IF(ISNA(MATCH(C3,AA$3:AA$500,)),1,3))

­ã´£³¡ªL

¦^´_ 7# RCRG


´£¨Ñªº¬O¤u§@ªí¤½¦¡, ­Y­n¼g¨ìVBA¤¤, ¹J¦³"??"¥]ÂЪº¤å¦r, ³£¶·¦A¥[¤@¼h""??"", ³o¬O³q«h,
¤]¥i¥ý¨Ï¥Î¿ý»s¦A¶K¤J!

RCRG

Sub TEST()
With
¡@¡@¡@.Formula = "=IF(C3="""","""",3^(1-COUNT(MATCH(C3,AA$2:AA$500,))))"
¡@¡@¡@. ...
­ã´£³¡ªL µoªí©ó 2015-11-12 10:09

    ÁÂÁ­ã¤jªº¸ÑÃD¡I

§Ú·Q¤F¸Ñ¥t¤@ºØ¤£¥²«ö¶sªº°µªk¡A´N¬O¤£¥Î«öÁä¤]·|ª½±µÀ°§Ú¶ñ¤J¡A¦ÓIJµo¶ñ¤J®É¶¡ÂI¬°¡G¨C·íSheet2ªºC3:C200¥ô¦ó¤@Ä椺®e¦³²§°Ê®É¡AB3:B79´N¥þ³¡¦A­«·s¶ñ¤J¤@¦¸¡C

¦³ÂIÀt¤ò¡A¦ýÁÙ¬O¥ý¸ò¦U¦ì¤j¤j»¡Án©êºp¡A³Â·Ð«ü±Ð¤F¡I>"<

­ã´£³¡ªL

¦^´_ 9# RCRG


Private Sub Worksheet_Change(ByVal Target As Range)
With Target
¡@¡@¡@If Not Intersect([AA2:AA500], .Cells) Is Nothing Then
¡@¡@¡@¡@¡@With [Sheet2!B3:B79]
¡@¡@¡@¡@¡@¡@¡@¡@.Formula = "=IF(C3="""","""",3^COUNT(MATCH(C3,AA$2:AA$500,)))"
¡@¡@¡@¡@¡@¡@¡@¡@.Value = .Value
¡@¡@¡@¡@¡@End With
¡@¡@¡@¡@¡@[Sheet2!D1] = Now
¡@¡@¡@End If
End With
End Sub