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

"find "ªº°ÝÃD

"find "ªº°ÝÃD

¦U¦ì¤j¤j:

¥»¤H¬O¾ÇªÌ,¦³¤@­Ó°Ý·Q°Ý°Ý, ªþ¥ó¤¤,¦pªG§Ú·Q¦bsheet2ªº¸ê®Æ¬Û¤ñsheet1 ªº¸ê®Æ,¦pªGsheet2¦³¦Ósheet1¨S¦³,¨º´Nsheet2·|Åã¥Üµµ¦â¼Ð°O

¦ý§Úµoı¥u¯à°µ¨ì¦³¸ê®Æ´N·|¦³¼Ð°O,¤£½×¬O§_¬Û¦Pªº¸ê®Æ¤]·|¼Ð°O,

½Ð¦U¦ì¤j¤jÀ°¦£«ü±Ð,ÁÂÁÂ

test.rar (15.16 KB)

¦^´_ 1# mdr0465
  1. Sub Ex()
  2.     Dim rng, rng2 As Range
  3.    
  4.     Application.ScreenUpdating = False
  5.    
  6.     With Sheets(2)
  7.      .Range("a1:a18").Interior.ColorIndex = xlNone
  8.        For Each rng2 In .Range("a1:a18").SpecialCells(xlCellTypeConstants)
  9.          If Sheets(1).Range("A1:A18").Find(rng2, lookat:=xlWhole) Is Nothing Then rng2.Interior.ColorIndex = 47
  10.        Next
  11.     End With
  12.    
  13.     Application.ScreenUpdating = True

  14. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

®æ¦¡¤ÆµLªk¸ó­¶ ¤u§@ªí2!C1:C7³]»²§U=COUNTIF(¤u§@ªí1!A:A,A1)

A1:A7®æ¦¡¤Æ=A1=C1
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

Sub Ex1_20171031()
   
    Application.ScreenUpdating = False
   
    Worksheets(2).Select
   
    ROW1 = Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row
   
    Cells.ClearFormats

    Sheets(1).Select

    For I = 1 To ROW1
        f1 = Worksheets(2).Cells(I, "A").Value
        Range("B1").Select
        On Error Resume Next                    '©¿²¤¿ù»~Ä~Äò°õ¦æ VBA ¥N½X,ÁקK¥X²{¿ù»~«H®§
        '·j´M¦r¦ê========================================================================
        Cells.Find(What:=f1, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , MatchByte:=False, SearchFormat:=False).Activate
        
        Worksheets(2).Cells(I, "B").Value = ActiveCell.Value
        If Worksheets(2).Cells(I, "B").Value = "" Then
            Worksheets(2).Range("A" & I).Interior.Color = RGB(128, 0, 128)
        End If
    Next
    Sheets(2).Select
    Range("B1:B" & ROW1).ClearContents
   
    Application.ScreenUpdating = True
End Sub

test.zip (19.76 KB)

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD