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

[µo°Ý] ½Ð±Ð¦U¦ì°ª¤â¤j¤jvba ªºindex°ÝÃD~

[µo°Ý] ½Ð±Ð¦U¦ì°ª¤â¤j¤jvba ªºindex°ÝÃD~

½Ð°Ý¦U¦ì¤j¤j¡A¯à§_½ÐÀ°¦£¸Ñ¨M¤@¤U¤p§Ìªº§xÂZ¡H

¥D­n°ÝÃD¬°¡G
¤À­¶(²§±`§å²Î­p) ªºL & M Ä椺ªº­È¥Î¥H¤Uªºµ{¦¡½X¥i¥H¨D±o¡A¦ý¤p§Ì·Q­n¨º¨âÄæ¦Û°Ê¥h´«ºâ°£¥HJÄ檺¸ê®Æ¡B
¨Ò¡G(²§±`§å²Î­p)ªºL 2¸ê®Æ / J2¸ê®Æ¡B(²§±`§å²Î­p)ªºM 2¸ê®Æ / M2¸ê®Æ ¡A(©¹¤UÃþ±À)
¦ý¤@ª½¤£ª¾¹D¬O¥d¦b­þ³¡¥÷¡B¬G¦b¦¹¨D§U¡A·PÁ¦U¦ì¡C

µ{¦¡½X¡G
Public Sub ²§±`§å²Î­p_1§P©w()
Dim ARR
Dim ARR1
Dim arr2
Dim arr3 'test
Dim arr4 ' test1
Dim c As Integer

    For i = 2 To [h65536].End(3).Row
    Set findvalue = Worksheets("§å²§±`Data").Columns("O").Find(What:=Worksheets("²§±`§å²Î­p").Cells(i, 8))
   
    If findvalue.Row = 1 Then
     'With Worksheets("§å²§±`Data") (¥Ø«e¤£»Ý¨Ï¥Î)
'       arr2 = Worksheets("§å²§±`Data").Range("b" & 1, "g" & findvalue.Row + 1) 'index (¥Ø«e¤£»Ý¨Ï¥Î)
'       ARR = Worksheets("§å²§±`Data").Range("O" & 1, "O" & findvalue.Row + 1) 'match (¥Ø«e¤£»Ý¨Ï¥Î)
        Else
      
       arr2 = Worksheets("§å²§±`Data").Range("H" & findvalue.Row - 1, "I" & findvalue.Row)
       ARR = Worksheets("§å²§±`Data").Range("O" & findvalue.Row, "O" & findvalue.Row + 1)
       c = Application.Match(findvalue, ARR, 0)
                          
       Worksheets("²§±`§å²Î­p").Range("L" & i, "M" & i) = Application.Index(arr2, c + 1)   '°ÝÃDÂI...
                     

    End If

Next
End Sub

¯u¬O¤£¦n·N«ä¡A¨S¦³ªþ¨ìªþÀÉ¡A¦b¦¹ªþ¤W¡B¦A½Ð³Â·Ð¤@¤U¤j®a¤F¡C

´ú¸Õª©.rar (42.96 KB)

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-5-6 20:57 ½s¿è

¦^´_ 2# ii31sakura

     §A¸Õ¸Õ
    ARR = Worksheets("§å²§±`Data").Range("O" & findvalue.Row & ":O" & findvalue.Row + 1)

TOP

¦^´_ 3# owen06


    ¤£¦n·N«ä owen06 ¤j¤j¡A½Ð°Ý§Ú¬O¤£¬O­þÃ䦳¿ù»~¡B¦]§Ú·Ó³o­Ó¤èªk¥h°õ¦æµ²ªGÁÙ¬O¤@¼Ë¡A
§Ú¤w±N¤º®e­×§ï©ñ¨ìªþ¥ó¡B¦pªG¥i¥H¦A½Ð³Â·ÐÀ°§Ú¬Ý¤@¤U¡A·PÁ§A~

´ú¸Õª©.zip (43.73 KB)

­×§ï«á

TOP

¦^´_ 4# ii31sakura


    «Ü©êºp¡A§Ú¨Sª`·N¬Ý¨ì§A¬O­n¨º¤GÄæ³£°£¥HjÄæ¡A
    §A¦b¡yWorksheets("²§±`§å²Î­p").Range("L" & i, "M" & i) = Application.Index(arr2, c + 1)   '°ÝÃDÂI..¡z   ªº¤U­±¿é¤J
        Cells(i, 12) = Cells(i, 12) / Cells(i, 10)
        Cells(i, 13) = Cells(i, 13) / Cells(i, 10)
    ¥L´N·|§â¨º¤GÄæ³£°£¥H¹ïÀ³ªºjÄ椺®e¤F

TOP

owen06 ¤j¤j¡B¤ÓÁÂÁ§A¤F~­ì¨Ó³oºØ¼gªk¤]¦æ¡BÅý¤p§Ì¾Ç¨ì¤F¡A·PÁÂÀ°¦£Åo~

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD