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

[µo°Ý] ¥u·Q­n§ì¨ú²Å¦X±ø¥óªº"²Ä¤@µ§"¨ä¥L¤£­n

[µo°Ý] ¥u·Q­n§ì¨ú²Å¦X±ø¥óªº"²Ä¤@µ§"¨ä¥L¤£­n

¦U¦ì°ª¤â«e½ú~
«÷«÷´ê´ê§Ë¤F¤U­±ªºµ{¦¡½X,¥i¥Hº¡¨¬¨ì¥Ø«eªº»Ý¨D~¦ý¬O°õ¦æ³t«×¦nºC~
¥Ø«e¥i¥H­×¥¿§Úªºµ{¦¡½X~¥Î§ó¦nªº¤è¦¡½Õ¾ã¶Ü?

§Ú·Q­n±N  ²Å¦X§Úµ¹ªº±ø¥ó ¥h A row´M§ä
§ä¨ì²Å¦X§Ú­nªº­È®É,´N©¹¤W·j¯Á¨ì²Å¦X±ø¥óªºÄæ¦ì,§â³o­ÓÄæ¦ìªº­È §ì¨«
¥i¬O§Ú¥u­n§ì²Å¦X±ø¥óªº²Ä¤@µ§¸ê®Æ,¨ä¥Lªº¤£­n~
´N³o¼Ë,§Ú¥Î¤F¿ý»s»E¶°ªº¤è¦¡~~¶Ì¶Ì¦a§¹¦¨¤F
¥i¬O³o¤£¬O§Ú·Q­nªº³t«×~¦nºC³á
¦³¾÷·|½Õ¾ã¶Ü?Àµ¨D«üÂI¤@¤G

¦³·Q¹L¥á¨ì°}¦C¥h~¦ý¥Ø«eÁÙ¤£·|¦p¦ó¥h°}¦C·j¯Á²Å¦X§Ú­nªº²Ä¤@µ§¸ê®Æ~¦]¬°·|¦³«Ü¦hµ§Ãþ¦ü
Àµ¨D«üÂI¤@¤G
Å¥¦P¾Ç»¡~~¤èªk«Ü¦hºØ~¦ý§ÚÁÙ¨S¦³¶}¬ ORZ:'(

Sub TEST ()
Dim zz As String

For i = 2 To Cells(Rows.Count, 10).End(xlUp).Row
    zz = Cells(i, 10).Value
    Columns("A:A").Select

    Selection.Find(What:=zz, after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate

    Cells.Find(What:="  (net ", after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate

    Cells(i, 11) = ActiveCell
    Cells(i, 11) = Right(Cells(i, 11), Len(Cells(i, 11)) - 7)

  Next
  Cells(1, 10).Select


End Sub

¦^´_ 1# boblovejoyce
§ì¨ú²Å¦X±ø¥óªº"²Ä¤@µ§", ¤£²M·¡§AªºÅÞ¿è ,¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Ar() As Variant, Ay() As Variant, i As Single, Msg As Variant
  4.     With Sheet1
  5.         Ar = .Range(.Cells(2, 10), .Cells(Rows.Count, 10).End(xlUp))  '°}¦C¤¸¯À¥ÑÀx¦s®æ¨ú±o¬°¤Gºû°}¦C
  6.         Ar = Application.WorksheetFunction.Transpose(Ar)           'Âà¸m¬°¤@ºû°}¦C
  7.         If Join(Ar, "") = "" Then  'Join ¨ç¼Æ ¶Ç¦^¤@­Ó¦r¦ê¡A¸Ó¦r¦ê¬O³z¹L³sµ²¬Y­Ó°}¦C¤¤ªº¦h­Ó¤l¦r¦ê¦Ó«Ø¥ßªº¡C
  8.             MsgBox "¨S¸ê®Æ"
  9.             Exit Sub
  10.         End If
  11.         ReDim Ay(1 To UBound(Ar))   '¦PAr¤j¤pªº°}¦C
  12.         For i = 1 To UBound(Ar)
  13.             With .[A:A]
  14.                 'After:=.Cells(.Cells.Count) ±q³Ì«á¤@­ÓÀx¦s®æ
  15.                 If Not .Find(Ar(i), After:=.Cells(.Cells.Count), lookat:=xlPart) Is Nothing Then
  16.                     Ay(i) = .Find(Ar(i), After:=.Cells(.Cells.Count), lookat:=xlPart)
  17.                     Ay(i) = Right(Ay(i), Len(Ay(i)) - IIf(Len(Ay(i)) > 7, 7, 0))
  18.                 End If
  19.             End With
  20.         Next
  21.         .Cells(2, 11).Resize(UBound(Ar), 1).Value = Application.WorksheetFunction.Transpose(Ay)
  22.         '°}¦C¤¸¯À¥Ñ¥ª¦Ó¥k:¥Ñ¤W¦Ó¤U¶·Âà¸m
  23.     End With
  24. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

sub aa()
dim data(100)

for i = 2 to sheet1.range("A65536").end(xlup).row
         if sheet1.cells(i,1)="§Aªº±ø¥ó"  then
                 a=a+1
                 data(a)=sheet1.cells(i,1)
         endif
next

for i = 1 to a
      <§A­n¿é¥Xªº¦ì¸m>=data(i)
next
end sub

±z¥i¥H¸Õ¬Ý¬Ý³o¼Ë

TOP

¦^´_ 1# boblovejoyce
©êºp¨S¬Ý¨ì±z¥u­n²Ä¤@µ§
­×¥¿¦p¤U

sub aa()
dim data(100)

for i = 2 to sheet1.range("A65536").end(xlup).row
         if sheet1.cells(i,1)="§Aªº±ø¥ó"  then
                 a=a+1
                 data(a)=sheet1.cells(i,1)
                 goto lab1
         endif
next

lab1:
for i = 1 to a
      <§A­n¿é¥Xªº¦ì¸m>=data(i)
next
end sub

±z¥i¥H¸Õ¬Ý¬Ý³o¼Ë

TOP

¦^´_ 2# GBKEE

ÁÂÁ¶W¯ÅªO¥Dªº¸Ñµª,¥¿¦b©¹³Ì«á¸Ñµª«e¶i¤¤
¤£¦n·N¦ü,¦]¬°§Ú¨S±Ô­z²M·¡ §Úªº²Ä¤@µ§±ø¥ó¬O¤°»ò?
§ÚRUN¤Fª©¥Dªºµ{¦¡½X,·|§ì¨ú¸ê®Æ~¦ý¦]¬°©w¸qªº²Ä¤@µ§±ø¥ó¨S¦³»¡²M·¡
©Ò¥H§ìªº¸ê®Æ¤£¬O»Ý­nªº~~§Ú¦³®³¿ö¥h­pºâ®É¶¡~¶]§¹¬ù­n15¬íÄÁ~
Á|¨Ò:
§Ú·Q­n¥hCells(2, 10)¤@ª½¨ì¬Û¹ïÀ³ªº³Ì«á¤@µ§¸ê®Æ,·í¦¨§Ú·j¯Áªº±ø¥ó
°²³]    Cells(2, 10)=¤ý¤p©ú

³o­Ó®É­Ô,§Ú´N·|¨Ì¾Ú "¤ý¤p©ú" ,¥hAÄæ·j¯Á¨ì "¤ý¤p©ú"
¥i¬O "¤ý¤p©ú" ¥u¬O§Ú¦bAÄ檺IJµo±ø¥ó
§Ú·|¨Ì·Ó"¤ý¤p©ú"³o­ÓAÄ椤ªºÀx¦s®æ¦ì¸m,¦A«×Ä~Äò©¹¤W´M§ä "  (net "
¥i¬O³o­Ó®É­ÔÁ`¦@·|¹J¨ì¤T¦¸"  (net
"  (net ¤p¥m¾´
"  (net ªüºÖ
"  (net ¤j¶¯

¦ý§Ú¥u·Q­n²Ä¤@¦¸¹J¨ìªº"  (net  ,¦b³o­Ó½d¨Ò¤¤´N¬O¤j¶¯,¤j¶¯´N¬O§Ú·Q­n¥á¨ìCells(2, 11)ªº­È
¦]¬°®æ¦¡¬O©T©wªº,"  (net    <--³o¬O©T©wªº
«á­±ªº¤H¦W³£·|ÀH¾÷ÅÜ°Ê~

«Ü§ïÁ¶W¯ÅªO¥D¦^ÂЧÚ~~3Q

TOP

¦^´_ 4# gn001038600

·PÁÂgn001038600¤j¤jªº¦^ÂÐ

¦]¬°§Úªº±ø¥ó¤w¸g¨S°ÝÃD¤F
¥i¬O§Ú­n¨Ï¥Î³o­Ó"±ø¥ó" Ä~Äò¦b AÄæ¦ì¤¤ ¥H"±ø¥ó"³o­ÓÀx¦s®æ©Ò¦b¦ì¸m test0512.rar (653.7 KB)
Ä~Äò"©¹¤W·j¯Á"...ª½¨ì§Ú·j¯Á¨ì²Ä¤@µ§ "±ø¥ó2"ªº®É­Ô,§â¥¦§ì¨«

±ø¥ó2  (²Ä3¦¸¹J¨ì)
±ø¥ó2  (²Ä2¦¸¹J¨ì)
±ø¥ó2  (²Ä1¦¸¹J¨ì)

§Ú¥u·Q­n§ì¨« ²Ä1¦¸¹J¨ì ªº±ø¥ó2 ,©ñ¨ì§Ú·Q­n©ñªº¦a¤è

§Ú´£¨Ñ¤F§Ú­ì©lªº½d¨Ò

TOP

¦^´_ 5# boblovejoyce

ªO¥D¤j¤j
¶W·PÁ§Aªº¦^ÂÐ
§Ú±Ô­zªº¥i¯à¤£°÷²M·¡
§Ú§â§Úªº½d¨Ò¥ý¥á¤W¨Ó~­Y¦³ªÅ¶¢ ¥i¥HÀ°§ÚØp¤@¤U¶Ü?
test0512.rar (653.7 KB)

TOP

¦^´_ 7# boblovejoyce

¸Õ¸Õ
  1. Sub test()
  2. i = 0
  3. Dim arr2()
  4. Set Rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  5. arr = Range("J2:J" & Cells(Rows.Count, 10).End(xlUp).Row)
  6. For Each ar In arr
  7. ReDim Preserve arr2(i)
  8.     Set zz = Rng.Find(ar, , , , , 2)
  9.     Set aa = Rng.Find("  (net ", Range(zz.Address), , , , 2)
  10.     arr2(i) = aa
  11.     arr2(i) = Right(arr2(i), Len(arr2(i)) - 7)
  12.     i = i + 1
  13. '    '²¾°£«e­±8­Ó¦r¤¸
  14.   Next
  15.    Range("K2").Resize(UBound(arr2) + 1) = Application.WorksheetFunction.Transpose(arr2)
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 7# boblovejoyce

°õ¦æ®É¶¡¬ù2.2¬í
  1. Sub test()
  2. t = Timer
  3. i = 0
  4. Dim arr2()
  5. Set Rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  6. arr = Range("J2:J" & Cells(Rows.Count, 10).End(xlUp).Row)
  7. For Each ar In arr
  8. ReDim Preserve arr2(i)
  9.     Set zz = Rng.Find(ar, , , , , 2)
  10.     Set aa = Rng.Find("  (net ", Range(zz.Address), , , , 2)
  11.     arr2(i) = aa
  12.     arr2(i) = Right(arr2(i), Len(arr2(i)) - 7)
  13.     i = i + 1
  14. '    '²¾°£«e­±8­Ó¦r¤¸
  15.   Next
  16.    Range("K2").Resize(UBound(arr2) + 1) = Application.Transpose(arr2)
  17. MsgBox Format(Timer - t, "0.0000")
  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# boblovejoyce

¤ÏÂЪº°õ¦æ§Aªº­ìµ{¦¡¡A°õ¦æªº®É¶¡«Üªøªº­ì¦]¬O¸ê®Æ¹L¦h¡Aªñ8¸U¦æªº¸ê®Æ
¦Ó²Ä¤@­ÓFind´M§ä¤S¬O¦V¤U´M§ä¡Aµ²ªG§äªº¸ê®Æ³£«á­±¡A¥ú¬O²Ä¤@­ÓFind´N¥Î±¼«Ü¦h®É¶¡
§Ú¸Õ¤F¦V¤W´M§ä¡A¾ãÅ骺®É¶¡®t¤Fªñ20¬í¡AÁÙ¦³Select¡B Selection¡BActivate¤]·|¦]¬°¿Ã¹õªº³B²z³t«×¼vÅT¨ì¾ãÅé´M§äªº³t«×

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD