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

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

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

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

  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

TOP

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

TOP

ÁÂÁ¨â¦ìªº¦^µª¡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

TOP

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


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

TOP

¦^´_ 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))

TOP

¦^´_ 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))

TOP

¦^´_ 7# RCRG


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

TOP

¥»©«³Ì«á¥Ñ RCRG ©ó 2015-11-21 11:16 ½s¿è
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>"<

TOP

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD