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

[µo°Ý] µ{§Ç¨S°ÝÃD¡Aµª®×Óý¦³°ÝÃD????

[µo°Ý] µ{§Ç¨S°ÝÃD¡Aµª®×Óý¦³°ÝÃD????

µ{§Ç¸ò¨Ó·½¨S°ÝÃD¡Aµª®×Óý¦³°ÝÃD???? Book2.zip (44.82 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-10-28 23:38 ½s¿è
µ{§Ç¸ò¨Ó·½¨S°ÝÃD¡Aµª®×Óý¦³°ÝÃD????
t8899 µoªí©ó 2014-10-28 20:46

¨â­ÓÄpµ²ÂI:
1. Range ªº find ¨ç¼Æ¥u¯à§ä¨ì²Ä 1 ­Ó²Å¦X±ø¥óªºÀx¦s®æ(°²³]¬° a),
­Y­n§ä²Ä 2 ­Ó²Å¦X±ø¥óªºÀx¦s®æ«h·j´M½d³ò­n§ï¦¨±q a ªº¤U¤@®æ¶}©l§ä°_.

2. Find ¨ç¼Æ­n¥Î  LookAt:=xlWhole(¦¹«D¹w³]­È) ¤~¬O§ä "¾ã­ÓÀx¦s®æ³£²Å¦X±ø¥ó" ªºÀx¦s®æ.
¬Gµ{¦¡§ï¬°:
Dim a, b, K, c
Set a = [a1]  ' ¦]©³¤U IIF ¤¤  a.Offset(1) ¦¹¶µ·|°õ¦æ­pºâ, ¬G¶·¥ý©w¸q a ªºªì­È,§_«h·|¦³¿ù»~.
For K = 1 To 50
b = Application.Large(Range("g2:g857"), K)
If Not IsNumeric(b) Then GoTo 110
Set a = Range(IIf(c = b, a.Offset(1), Range("g2")), [g857]).Find(What:=b, LookIn:=xlValues, LookAt:=xlWhole)
...
c = b
End If
Next
...

TOP

¨â­ÓÄpµ²ÂI:
1. Range ªº find ¨ç¼Æ¥u¯à§ä¨ì²Ä 1 ­Ó²Å¦X±ø¥óªºÀx¦s®æ(°²³]¬° a),
­Y­n§ä²Ä 2 ­Ó²Å¦X±ø¥ó ...
luhpro µoªí©ó 2014-10-28 23:36


Set a = Range(IIf(c = b, a.Offset(1), Range("g2")), [g857])
¦¹³B»Ý­nª«¥ó??

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-10-29 13:50 ½s¿è

¦^´_ 3# t8899
Large¨ç¼Æ ¤£¬O¯uªº¶Ç¦^¼Æ­È¸ê®Æ¤¤ªº²Ä´X¤j
  1. Sub EX()
  2.     Dim AR, k
  3.     AR = Array(5, 5, 6, 6, 7, 7, 8)
  4.    ' AR = Array(5, 6, 7, 5, 6, 7, 8)
  5.     For k = 1 To UBound(AR) + 1
  6.     MsgBox "²Ä " & k & " ¤j : " & Application.Large(AR, k)
  7.     Next
  8. End Sub
½Æ»s¥N½X
¶·­×§ï¤@¤U
  1. Option Explicit
  2. Dim D As Object
  3. Private Sub CommandButton1_Click()
  4.     Range("K3:S" & Rows.Count).ClearContents
  5.     Dim a As Range, b As Long, k As Integer, aD As String
  6.     ±Æ§Ç­È Range("g2:g857")
  7.     For k = 1 To IIf(D.Count >= 50, 50, D.Count)
  8.         b = Application.Large(D.KEYS, k)
  9.         Set a = Range("g2:g857").Find(What:=b, LookIn:=xlValues, lookat:=xlWhole)
  10.         If Not a Is Nothing Then aD = a.Address
  11.         Do While Not a Is Nothing
  12.             Range("K100").End(xlUp).Offset(1) = a.Offset(0, -6) '¥N¸¹
  13.             Range("L100").End(xlUp).Offset(1) = a.Offset(0, -5) '¦WºÙ
  14.             Range("M100").End(xlUp).Offset(1) = a.Offset(0, 0) ' ±i
  15.             Range("N100").End(xlUp).Offset(1) = a.Offset(0, -4) '»ù¦ì
  16.             Set a = Range("g2:g857").FindNext(a)
  17.             If a.Address = aD Then Exit Do
  18.         Loop
  19.     Next
  20.     ±Æ§Ç­È Range("h2:h857")
  21.     For k = 1 To IIf(D.Count >= 50, 50, D.Count)
  22.         b = Application.Large(D.KEYS, k)
  23.         Set a = Range("h2:h857").Find(What:=b, LookIn:=xlValues)
  24.         If Not a Is Nothing Then aD = a.Address
  25.         Do While Not a Is Nothing
  26.             Range("P100").End(xlUp).Offset(1) = a.Offset(0, -7) '¥N¸¹
  27.             Range("Q100").End(xlUp).Offset(1) = a.Offset(0, -6) '¦WºÙ
  28.             Range("R100").End(xlUp).Offset(1) = a.Offset(0, 0) ' ±i
  29.             Range("S100").End(xlUp).Offset(1) = a.Offset(0, -2) '»ù¦ì
  30.             Set a = Range("h2:h857").FindNext(a)
  31.             If a.Address = aD Then Exit Do
  32.         Loop
  33.     Next
  34. End Sub
  35. '***********
  36. Private Sub ±Æ§Ç­È(Rng As Range) '±Æ°£¦³­«½Æªº¼Æ­È
  37.     Dim e As Range
  38.     Set D = CreateObject("SCRIPTING.DICTIONARY")
  39.     For Each e In Rng.SpecialCells(xlCellTypeConstants)
  40.        If IsNumeric(e) Then D(e.Value) = ""
  41.     Next
  42. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  t8899
Large¨ç¼Æ ¤£¬O¯uªº¶Ç¦^¼Æ­È¸ê®Æ¤¤ªº²Ä´X¤j¶·­×§ï¤@¤U
GBKEE µoªí©ó 2014-10-29 13:47


²Ä 6 ¦æ¡A¸ò²Ä20¦æ ???  ±Æ§Ç­È Range("g2:g857")

TOP

¦^´_ 5# t8899
   
  1. For K = 1 To 50
  2. b = Application.Large(Range("g2:g857"), K)
  3. If Not IsNumeric(b) Then GoTo 110
½Æ»s¥N½X

Large¨ç¼Æ ¤£¬O¯uªº¶Ç¦^¼Æ­È¸ê®Æ¤¤ªº²Ä´X¤j,§A¬O§_¤F¸Ñ
§AªºªþÀɵ{¦¡½X¥Î Large¨ç¼Æ , ·í K=13,K=14    b³£¬O=75,
¦]¬° "g2:g857" ¦³2­Ó75, ¥tÁÙ¦³2­Ó2,©Ò¥H³£¦³­«½Æ¥X²{.
'*****************************************
±Æ§Ç­È Range("g2:g857")
If IsNumeric(e) Then D(e.Value) = ""  
¥Î¦r¨åª«¥ó¦³¬Û¦PªºKEY(¬Û¦Pªº¼Æ¦r)¨Ó®ø°£­«½Æªº¼Æ¦r
b = Application.Large(D.KEYS, k)
¦p¥u¯u¥¿ªº¨Ì§Ç¶Ç¦^"g2:g857"¼Æ­È¸ê®Æ¤¤ªº²Äk¤j
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2014/10/29 20:07): ­þ¤@¦æªº¿ù»~!

¦^´_  t8899
   
Large¨ç¼Æ ¤£¬O¯uªº¶Ç¦^¼Æ­È¸ê®Æ¤¤ªº²Ä´X¤j,§A¬O§_¤F¸Ñ
§AªºªþÀɵ{¦¡½X¥Î Large¨ç¼Æ  ...
GBKEE µoªí©ó 2014-10-29 15:08



³øºp¡A§ÚªºG,HÄæ¬O¤½¦¡ =IF(C2<>E2,"",XQKGIAP|Quote!'1101.TW-BestBidSize1')
®M¤W¥h¥X²{¿ù»~¡A¤£ª¾¦p¦ó­×§ï?
#1004#
À³¥Îµ{¦¡ª«¥ó©w¸q¿ù»~

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-10-29 21:17 ½s¿è
Set a = Range(IIf(c = b, a.Offset(1), Range("g2")), [g857])
¦¹³B»Ý­nª«¥ó??
t8899 µoªí©ó 2014-10-29 06:10

©³¤U³o¤@¦æ­n©ñ¦b³Ì«e­±,
¦³©ñ¤F¶Ü?
Set a = [a1]  ' ¦]©³¤U IIF ¤¤  a.Offset(1) ¦¹¶µ·|°õ¦æ­pºâ, ¬G¶·¥ý©w¸q a ªºªì­È,§_«h·|¦³¿ù»~.

¦^´_  t8899
Large¨ç¼Æ ¤£¬O¯uªº¶Ç¦^¼Æ­È¸ê®Æ¤¤ªº²Ä´X¤j,§A¬O§_¤F¸Ñ
§AªºªþÀɵ{¦¡½X¥Î Large¨ç¼Æ  ...
GBKEE µoªí©ó 2014-10-29 15:08

¥H§Ú¬Ý¼Ó¥Dªº±¡§Î¨Ã¤£¾A¦XÂo±¼¬Û¦P¼Æ­Èªº¸ê®Æ,
¦Ó¬O­n¤@¤@Åã¥Ü¥X¨Óªº³á.

¨â­Ó¤£¦PªÑ²¼ªº¬Y­Ó­È¥i¯à¬Û¦P,
¦ý«o¬O³£»Ý­n¦C¥X¨Óªº,
¤£¯à¦]¬°¬Y­Ó­È¬Û¦P´N¥u¦C¤@¤äªÑ²¼ªº¸ê®Æ,
³o¤äªÑ²¼¤£¯à¨ú¥N¨ä¥LªÑ²¼ªº¸ê®Æ.

©Ò¥H§Ú¤~§â Find ¨ç¼ÆªºRange½d³ò³]¬° : ¤W¦¸¬Û¦P­Èªº¤U¤@­ÓÀx¦s®æ¶}©l¦Ü³Ì¥½®æ¤î.

³øºp¡A§ÚªºG,HÄæ¬O¤½¦¡ =IF(C2<>E2,"",XQKGIAP|Quote!'1101.TW-BestBidSize1')
®M¤W¥h¥X²{¿ù»~¡A¤£ª¾¦p¦ó­×§ï?
#1004#
À³¥Îµ{¦¡ª«¥ó©w¸q¿ù»~
t8899 µoªí©ó 2014-10-29 20:01

¸g§Ú¹ê»Ú´ú¸Õ¹L³o¤@¦æ¤£¸Óµo¥Í¦¹¿ù»~,
¤£¦p¤W¶ÇÀɮ׬ݬÝ?

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-10-30 06:55 ½s¿è

¦^´_ 8# luhpro
   
¥H§Ú¬Ý¼Ó¥Dªº±¡§Î¨Ã¤£¾A¦XÂo±¼¬Û¦P¼Æ­Èªº¸ê®Æ,
¦Ó¬O­n¤@¤@Åã¥Ü¥X¨Óªº³á.
¦³¤@¤@Åã¥Ü¥X°Ú


  1. Set a = Range("g2:g857").Find(What:=b, LookIn:=xlValues, lookat:=xlWhole)
  2.     If Not a Is Nothing Then aD = a.Address
  3.     Do While Not a Is Nothing
  4.         Range("K100").End(xlUp).Offset(1) = a.Offset(0, -6) '¥N¸¹
  5.         Range("L100").End(xlUp).Offset(1) = a.Offset(0, -5) '¦WºÙ
  6.         Range("M100").End(xlUp).Offset(1) = a.Offset(0, 0) ' ±i
  7.         Range("N100").End(xlUp).Offset(1) = a.Offset(0, -4) '»ù¦ì
  8.         '***************************************************
  9.         Set a = Range("g2:g857").FindNext(a)  '³o¸Ì¦³¦A©¹¤U¬Û¦P­ÈªºÀx¦s®æ
  10.         If a.Address = aD Then Exit Do  '¦^¨ì²Ä¤@­ÓÀx¦s®æ
  11.         '***********************************************
  12.     Loop
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¸g§Ú¹ê»Ú´ú¸Õ¹L³o¤@¦æ¤£¸Óµo¥Í¦¹¿ù»~,
¤£¦p¤W¶ÇÀɮ׬ݬÝ?
luhpro µoªí©ó 2014-10-29 21:03


½Ð´úÅç¬Ý¬Ý! Book2.zip (111.53 KB)

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD