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

½Ð°ÝVBA ´M§ä¸ê®Æªº°ÝÃD

½Ð°ÝVBA ´M§ä¸ê®Æªº°ÝÃD

½Ð°Ý¤@¤U VBA´M§ä¸ê®Æ
Set c = Worksheets("AA").Columns("F").Find(.Cells(i, 7), LookIn:=xlValues, LookAt:=xlWhole)
¬O«ü¨ì"AA"¤u§@ªí¤¤ªºFÄæ¦ì¤¤ ´M§ä²Å¦XCells(i, 7)±ø¥óªº­È
­ì¥»¬O¤@¹ï¤@·j´M
¥i¥H¤@¹ï¦h¾ã¦C±a¥X¶Ü¡H
¦pªþ¥ó

ÁÂÁÂ

find.jpg (26.96 KB)

ªþ¥ó´y­z

find.jpg

¦^´_ 1# tonycho33
¥ÎÀ£ÁYÀÉ µ¹­ÓExcleÀÉ®×

TOP

¦^´_ 1# tonycho33
  1. Sub nn()
  2. Range([J7], [J7].End(xlToRight)).ClearContents
  3. Set a = [F:F].Find([I7], lookat:=xlWhole)
  4. i = i + 1
  5. [I7].Offset(, i) = a.Offset(, 1)
  6. Do Until a.Offset(i) <> "" Or a.Offset(i, 1) = ""
  7.   [I7].Offset(, i) = a.Offset(i, 1)
  8.   i = i + 1
  9. Loop
  10. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# tonycho33

find.rar (6.59 KB)

TOP

¦^´_ 4# tonycho33
  1. Sub nn()
  2. Range([H5], [H5].End(xlToRight)).ClearContents '²M°£¤§«e¤º®e
  3. Set a = [B:B].Find([G5], lookat:=xlWhole) '§ä¨ìG5¦bBÄ檺¦ì¸m
  4. i = i + 1
  5. [G5].Offset(, i) = a.Offset(, 1) 'G5¦V¥k¼g¤J
  6. Do Until a.Offset(i) <> "" Or a.Offset(i, 1) = "" '°õ¦æ°j°éª½¨ìBÄ椣µ¥©óªÅ¥Õ©ÎCÄ椣µ¥©óªÅ¥Õ
  7.   [G5].Offset(, i) = a.Offset(i, 1) 'G5¦V¥k¼g¤J
  8.   i = i + 1
  9. Loop
  10. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh
½Ð°Ý¤@¤U¡A§Ú­Y·QÅýG5¥H¤U¨C­Ó³£¯à¹ïÀ³¡A¥[¤JjªºÅܼÆ,¥i¬O³o¼Ë¶]°_¨Ó«o¬O¥X²{¤@¹M«á´N®ø¥¢
³Â·Ð¸Ñµª ÁÂÁÂ

    Sub nn()
With Worksheets("Sheet1")
Range([H5], [H5].End(xlToRight)).ClearContents '²M°£¤§«e¤º®e

for j=j+1
Set a = [B:B].Find(.cell(j,5), lookat:=xlWhole) '§ä¨ìG5¦bBÄ檺¦ì¸m

i = i + 1

cell(j,5).Offset(, i) = a.Offset(, 1) 'G5¦V¥k¼g¤J

Do Until a.Offset(i) <> "" Or a.Offset(i, 1) = "" '°õ¦æ°j°éª½¨ìBÄ椣µ¥©óªÅ¥Õ©ÎCÄ椣µ¥©óªÅ¥Õ

  cell(j,5).Offset(, i) = a.Offset(i, 1) 'G5¦V¥k¼g¤J

  i = i + 1

Loop
next j
end with
End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# tonycho33
  1. Sub Ex()
  2.     Dim Criterion As Range, Rng As Range, E As Range
  3.     Set Criterion = Range([G5], [G5].End(xlDown))               'G5¥H¤Uªº½d³ò
  4.     Criterion.CurrentRegion.Offset(, 1) = ""                    'G5ªº³sÄò½d³ò©¹¥k1Ä椧½d³ò
  5.     For Each E In Criterion                                     '¨Ì§Ç³B²z G5¥H¤Uªº½d³òªºCell
  6.         Set Rng = [B:B].Find(E, lookat:=xlWhole)                '§ä¨ìG5¦bBÄ檺¦ì¸m
  7.         If Not Rng Is Nothing Then
  8.             i = 0
  9.             Do
  10.                 E.Offset(, i + 1) = Rng.Offset(i, 1)
  11.                 i = i + 1
  12.             Loop Until Rng.Offset(i) <> "" Or Rng.Offset(i, 1) = ""
  13.         End If
  14.     Next
  15. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD