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

¦p¦ó¥Îvlookup¦Û°Ê¦¬´M¤ñ¹ïªº¶µ¦¸¼Æ¶q

¦p¦ó¥Îvlookup¦Û°Ê¦¬´M¤ñ¹ïªº¶µ¦¸¼Æ¶q

½Ð°Ý¤@¯ë¨Ï¥Îvlookup®É¡A¨Ï¥Î·Æ¹«ÂI¨â¤U§Y¥i¾ãÄæ³£¦Û°Ê¶ñ¤J
¨Ï¥Î¿ý»s¥\¯à®É¡A¦]¬°¨C¦¸¶µ¦¸³£¤£¦P¡A¸Ó¦p¦ó¥h°õ¦æ?
¥Ø«e§Ú¬O¥Î¤U­±³o¼Ë¿ý»s¡A¦³¤ñ¸û©ú²Ó«ü¥OÅývba¯à°÷§ì¨ì¹ïÀ³ªºÄæ¦ì?

    Sheets("°U¥~³øªí").Select 'VLOOKUP¤ñ¹ï¸ê®Æ±ÄÁʸɥR
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-1],'°U¥~³øªí(Á`ªí)'!C[-1]:C,2,0)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B3000")
    Range("B2:B3000").Select
    Columns("B:B").Select  '§R°£N/A
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Sub BBB()
Sheet1.Select
Range("A1").Select
For X = 2 To 4
If 1 = 1 Then
  M = Sheet1.Cells(X, 1)
   mymax = Evaluate("VLOOKUP(""" & M & """,¤u§@ªí2!A:B,2,FALSE)")
   If Application.IsError(mymax) = True Then mymax = ""
   Sheet1.Cells(X, 2) = mymax
End If
Next
End Sub
ù

TOP

¦^´_ 2# rouber590324
ÁÂÁ¡Aµ¥µ¥´ú¸Õ¬Ý¬Ý
¦³°ÝÃD¦A½Ð±Ð

TOP

With Range([°U¥~³øªí!B2], [°U¥~³øªí!A65536].End(xlUp)(1, 2))  '¥HAÄæ¨ú±o³Ì«á¤@µ§¦ì¸m, ¦A¥k²¾¤@®æ  
     .FormulaR1C1 = "=VLOOKUP(RC[-1],'°U¥~³øªí(Á`ªí)'!C[-1]:C,2,0)"
     .Value = .Value
     .Replace "#N/A", ""
End With

TOP

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

With Range([°U¥~³øªí!B2], [°U¥~³øªí!A65536].End(xlUp)(1, 2))  '¥HAÄæ¨ú±o³Ì«á¤@µ§¦ì¸m, ¦A¥k²¾¤@®æ  
½Ð°Ý³o¤@¦æªº·N«ä§ï¬°(1,3)·|±N°U¥~³øªí¤¤,A-CÄæ¦ì¤@°_¤ñ¹ï¹L¨Óªº·N«ä¶Ü?

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-6-11 11:10 ½s¿è

¦^´_ 5# cclo0728

°õ¦æ¥H¤Uµ{¦¡¡A¬Ý­þ¨ÇÀx¦s®æ³Q¿ï¨ú´N¥i¥Hª¾¹D½d³ò¤F~¡A¥i¦Û¦æÅçÃÒ¤£¶·­n°Ý§O¤H

  Range([°U¥~³øªí!B2], [°U¥~³øªí!A65536].End(xlUp)(1, 3)).Select
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 5# cclo0728
¥i¦Û¦æ¥h´ú¸Õ¦³¦ó¤£¦P???

¦Aµ¹¨âºØ¤èªk:
Sub TTT1()
R& = [°U¥~³øªí!A65536].End(xlUp).Row
With [°U¥~³øªí!B2].Resize(R - 1)
     .FormulaR1C1 = "=VLOOKUP(RC[-1],'°U¥~³øªí(Á`ªí)'!C[-1]:C,2,0)"
     .Value = .Value
     .Replace "#N/A", ""
End With
End Sub

Sub TTT2()
R& = [°U¥~³øªí!A65536].End(xlUp).Row
With Sheets("°U¥~³øªí").Range("B2:B" & R)
     .FormulaR1C1 = "=VLOOKUP(RC[-1],'°U¥~³øªí(Á`ªí)'!C[-1]:C,2,0)"
     .Value = .Value
     .Replace "#N/A", ""
End With
End Sub

TOP

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

ÁÂÁ¡A§Ú²z¸Ñ¤F

TOP

¦^´_ 6# n7822123


    Range([°U¥~³øªí!B2], [°U¥~³øªí!A65536].End(xlUp)(1, 3)).Select
§Ú²z¸Ñ¤Fselect¡A·PÁ«ü¾É

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD