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

½Ð±Ð¦U¦ì«e½úvba Ãö©ó³]©w¥y¶°°ÝÃD~

½Ð±Ð¦U¦ì«e½úvba Ãö©ó³]©w¥y¶°°ÝÃD~

½Ð°Ý¦U¦ì¤j¤j¤p§Ìªº°ÝÃD¬O¡G
´ú¸Õµ{¦¡½Xªº¥\¯àµª®×¬Ookªº¡A¦ý¤p§Ì«o¥u¯à¦b¤À­¶("²§±`§å²Î­p" or "§å²§±`Data")¤U¥hRun¡A
¦pªG·Q­n·s¶}¤@­Ó¤À­¶¥hRun µ{¦¡½X" ²§±`§å²Î­p_1§P©w¡A¥¦³£¤£·|¥h²z¤p§Ì¡B°Ê¤]¤£°Ê¡K(¨Ò¡Gªþ¥ó¤¤ªº¤À­¶"Á`±±¨î")
½Ð°Ý¤p§Ì¸Ó¦p¦ó¥h³]©w©Î¬O©I¥s"²§±`§å²Î­p_1§P©w"¥i¥HÅý¥¦¦b¨ä¥¦¤À­¶³£¥i¥HRun©O¡H
·PÁ¤j®a~



´ú¸Õµ{¦¡½X¡G °ÝÃD¡G(¥\¯à¦bWorksheets("²§±`§å²Î­p")&Worksheets("§å²§±`Data") ¡A¬Ò¥i°õ¦æ¡A¦ý¦b¨ä¥¦¤À­¶¤@°Ê¤]¤£°Ê..)
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)
      Worksheets("²§±`§å²Î­p").Cells(i, 12) = Worksheets("²§±`§å²Î­p").Cells(i, 12) / Worksheets("²§±`§å²Î­p").Cells(i, 10)
       Worksheets("²§±`§å²Î­p").Cells(i, 13) = Worksheets("²§±`§å²Î­p").Cells(i, 13) / Worksheets("²§±`§å²Î­p").Cells(i, 10)
   
    End If

Next
End Sub

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

GBKEE ¤j¤j¯u¬O¤Ó·PÁ¤F~­ì¨Ó°ÝÃDÂI¥X¦b³oÃä¡A¤p§Ì¦A¨Ó­«³]¬Ý¬Ý¡B·PÁ§A~

TOP

¦^´_ 1# ii31sakura
   
¦pªG·Q­n·s¶}¤@­Ó¤À­¶¥hRun µ{¦¡½X" ²§±`§å²Î­p_1§P©w¡A¥¦³£¤£·|¥h²z¤p§Ì¡B°Ê¤]¤£°Ê¡K(¨Ò¡Gªþ¥ó¤¤ªº¤À­¶"Á`±±¨î")

¨Ò¡Gªþ¥ó¤¤ªº¤À­¶"Á`±±¨î"
  1. Public Sub ²§±`§å²Î­p_1§P©w()
  2. Dim ARR
  3. Dim ARR1
  4. Dim arr2
  5. Dim arr3 'test
  6. Dim arr4 ' test1
  7. Dim c As Integer
  8.     MsgBox [h65536].End(3).Row
  9.     For i = 2 To [h65536].End(3).Row  
  10.     ' *** ·í[h65536].End(3).Row  ¤p©ó 2 ³o Forªº°j°é¬O¤£·|°õ¦æªº ***
  11.     Set findvalue = Worksheets("§å²§±`Data").Columns("O").Find(What:=Worksheets("²§±`§å²Î­p").Cells(i, 8))
  12.    
  13.     If findvalue.Row = 1 Then
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD