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

[µo°Ý] ´M§ä¥¢¸¨ªº®y¼Ð

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-4-30 21:51 ½s¿è

Sub FindLoc()
  Dim iNum%, iY%, iDown%, iKey%, iI%, iLastY%, iAns%
  Dim c
  
  iDown = [C65536].End(xlUp).Row
  
  Range(Cells(2, 2), Cells(iDown, 3)).Sort _
     Key1:=Range("C1"), _
     Key2:=Range("B1")
  
  iNum = 2
  iLastY = 0
  iAns = 2
  
  Do
    iY = Cells(iNum, 3)
   
    If iLastY <> iY Then
      With Range(Cells(2, 5), Cells([E65535].End(xlUp).Row, 5))
        Set c = .Find(iY, LookIn:=xlValues)
        If Not c Is Nothing Then
          iKey = c.Row
        Else
          MsgBox ("Àx¦s®æ :  C" + Right(Str(iNum), Len(Str(iNum)) - 1) + " ªº Y ­È¬° " + Str(iY) + " §ä¤£¨ì!")
          Exit Sub
        End If
      End With
    End If
  
    For iI = Cells(iKey, 6) To Cells(iKey, 7)
      If Cells(iNum, 2) <> iI Then
        Cells(iAns, 9) = iI
        Cells(iAns, 10) = iY
        iAns = iAns + 1
      Else
        iNum = iNum + 1
      End If
    Next iI
    iLastY = iY
  
  Loop While iNum <= iDown
End Sub

¦pªG­n©ñ¦b¤£¦Pªº Sheet ½Ð¦A¦Û¦æ§ó§ï¤º®e.

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-5-1 23:35 ½s¿è
Hello Luhpro,
   ÁÂÁ§AªºÀ°¦£, µ{¦¡¶]¨ì¤@¥b¥X²{§ä¤£¨ìY­È


©êºp. §Ú¬Ý¿ù¥H¬°¤¤¶¡¨º­Óªí®æ­ì¥ý´N¦s¦b, ©Ò¥H¤W­±ªºµ{¦¡¬O»P¸Óªí®æ¤º®e°µ¤ñ¹ï¥H­P¦³¿ù»~

¥H¤U¬°­×¥¿«áªºµ{¦¡½X :
Sub FindLoc()
  Dim iSource%, iX%, iY%, iDown, iLastY%, iComp%, iAns%, iNum%
  
  iDown = [C65536].End(xlUp).Row ' §ä­ì©l¸ê®Æ³Ì©³ºÝ
  
  Range(Cells(2, 2), Cells(iDown, 3)).Sort _  ' ­ì©l¸ê®Æ±Æ§Ç
     Key1:=Range("C1"), _
     Key2:=Range("B1")
   
  iX = Cells(2, 2)      ' ²Ä 2 ¦C¸ê®Æª½±µ¥N¤J Y »P ³Ì¤p­È ªºX
  iLastY = Cells(2, 3)
  Cells(2, 5) = iLastY
  Cells(2, 6) = iX
  iComp = 2     ' ¶×Á`ªí®æ
  iAns = 2      ' ¯Ê¶µªí®æ
  iSource = 3   ' ±q²Ä 3 ¦C¶}©l
  iNum = iX + 1 ' »¼¼W¼Æ¦r¥H»P­ì©l¸ê®Æ¤ñ¹ï
   
  Do
  
    iY = Cells(iSource, 3) '§ì¤U¤@µ§¸ê®Æ
    iX = Cells(iSource, 2)
   
    If iLastY <> iY Then   ' Y ¦³·s­È
      Cells(iComp, 7) = Cells(iSource - 1, 2)  ' ¥N¤J¤W¤@­Ó Y ªº ³Ì¤j­È ªºX
      iComp = iComp + 1
      Cells(iComp, 5) = iY  '¥N¤J·sªº Y »P ³Ì¤p­È ªºX
      Cells(iComp, 6) = iX
      iNum = iX
    End If  
      
   
    Do While iNum <> iX  ' ¦³¯Ê¶µ, ¥N¤J¸ê®Æ¨ì¯Ê¶µªí®æ¤¤, ª½¨ì¤£¦A¦³¯Ê¶µ
      Cells(iAns, 9) = iNum
      Cells(iAns, 10) = iY
      iAns = iAns + 1
      iNum = iNum + 1
    Loop
   
    iSource = iSource + 1
    iNum = iNum + 1
    iLastY = iY
  
  Loop While iSource <= iDown   ' ¤ñ¸û§¹²¦
  Cells(iComp, 7) = Cells(iSource - 1, 2) ' ±a¤J³Ì«á¤@­Ó ³Ì¤j­È ªºX
End Sub

¦^ 9 ¼Ó cw3076
¤£«È®ð.

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD