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

excel vlook­È±o¤ñ¹ï°ÝÃD

excel vlook­È±o¤ñ¹ï°ÝÃD

¦U¦ì¤j­ô¦n
      ¤p§Ì¦³¤@­Ó°ÝÃD·QÅýSHEET A ªº«~¦W¹L¨ìSHEET B
¹Á¸Õ¤F¤@¨Ç±ø¥ó§@ªk¦pªþ¥ó¡A¦ý¬O·|¿ù¡C
¦³¨ÆµÛINDEXªº¤èªk¦ý¬O¤]°µ¤£¥X¨Ó·Q½Ð±ÐÀ³¦p¦ó¸Ñ ¬¡­¶Ã¯1.rar (9.54 KB)
lionliu

¦^´_ 1# lionliu


¬O³o¼Ë¶Ü??

Sub desc()
'pk ctn

Dim i, j, rcnt As Integer
Dim x As Variant
Sheets("B").Select
rcnt = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To rcnt
   x = 1
   Do While x <= Sheets("a").Range("a1").CurrentRegion.Rows.Count
      If Sheets("a").Cells(x, 1) = Sheets("B").Cells(i, 1) And IsNumberic(Cells(i, 3).Value) Then
         Cells(i, 2) = Sheets("A").Cells(x, 2)
         Cells(i, 2).Offset(1) = Sheets("A").Cells(x, 2).Offset(1)
      End If
      x = x + 1
   Loop
Next
Set x = Nothing
End Sub
Function IsNumberic(Value As String) As Boolean
   
    If Value Like "[+-]*" Then Value = Mid$(Value, 2)
    IsNumberic = Not Value Like "*[!0-9.]*" And Not Value Like "*.*.*" And _
    Len(Value) > 0 And Value <> "."
End Function

TOP

¦^´_ 1# lionliu
  1. Set x = Application.WorksheetFunction.VLookup(Columns("A:A"), A!Columns("A:B"),
½Æ»s¥N½X
Set³oÃöÁä¦r¬O±NÅܼƫü©w¨ìª«¥ó®É©Ò¥Îªº
WorksheetFunction.VLookup ¨ç¼ÆµLªk¶Ç¦^¤@ª«¥ó
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# jcchiang
ÁÂÁÂ jc¤j¤j
´ú¸Õ«á¤w¸g¥i¥H¥Î¤FÁÂÁ¡C
lionliu

TOP

¦^´_ 3# GBKEE
ÁÂÁÂgb¤j¤j«ü¾É³á¡A­Y¨Ì§Ú­ì¨Óªº¼gªk¥i¦æ¶Ü¡AÁöµMjc¤j¤jªº§ïªk¤w¥i¥H¶¶§Q§ì¨ì§Ú­nªº¸ê®Æ¤F¡C
lionliu

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD