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

[µo°Ý] ¦³Ãö¨Ï¥Î¨Æ¥óµ{§Ç¨ú±o¸ê®Æ(¦³¤G­Ó°ÝÃD)

[µo°Ý] ¦³Ãö¨Ï¥Î¨Æ¥óµ{§Ç¨ú±o¸ê®Æ(¦³¤G­Ó°ÝÃD)

¥»©«³Ì«á¥Ñ icestormer ©ó 2014-1-14 12:33 ½s¿è









¤W¹Ï¬O§Q¥ÎA2ªºª««~¦WºÙ ¥h§ä¨ì¸ê®Æ°Ï¤ºªº½æ»ù¦ý²{¦b¬O¶R»ùªº¤½¦¡¼g¤£¥X¨Ó~~

¤U¹Ï¬O¦UºØª««~¸ê®Æ ¥Ñ©ó¬õ°é³B¸ê®Æµ§¼Æ¤£¦P ©Ò¥H¤£ª¾À³­n«ç¼g¤½¦¡ÅýEXCEL¯à¨ú±o¡@¶R»ùªº»ù¦ì¡]¤wªþ¤WÀɮס^
¦pªG§Ú¦³»¡©ú¤£²Mªº¦a¤è¡A½Ð¨£½Ì¡ã¡ã¡@




Book2.rar (13.75 KB)

¦^´_ 1# icestormer
VBAProject
¥D¤u§@ªí
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.   If Application.Intersect(Target, Range("F1:G1")) Is Nothing Then Exit Sub
  3.   UpdatePrice
  4. End Sub
½Æ»s¥N½X
¤@¯ë¼Ò²Õ
  1. Sub UpdatePrice()
  2.   Dim d, rng As Range
  3.   Dim sName As String, sSell As String, sBuy As String
  4.   Dim r As Long, c As Long, x
  5.   
  6.   Const sMark = " - Market Browser"
  7.   Const sMarkSell = "Sell Orders (Buy Orders)"
  8.   Const sMarkBuy = "Buy Orders"
  9.   
  10.   Set d = CreateObject("scripting.dictionary")
  11.   
  12.   With Sheets("¸ê®Æ°Ï")
  13.     For c = 1 To .UsedRange.Columns.Count Step 7  '6Äæ¥[¤WªÅ¥ÕÄæ = 7
  14.       For r = 1 To .Cells(.Rows.Count, c).End(xlUp).Row Step 300 '©T©w300¦C
  15.         sName = "": sSell = "": sBuy = ""
  16.         With .Cells(r, c).Resize(300)
  17.           Set rngName = .Find(sMark, LookIn:=xlValues, lookat:=xlPart)
  18.           If rngName Is Nothing Then GoTo NEXT_BLOCK Else sName = Left(rngName.Value, Len(rngName.Value) - Len(sMark))
  19.          
  20.           Set rng = .Find(sMarkSell, LookIn:=xlValues, lookat:=xlWhole)
  21.           If Not rng Is Nothing Then sSell = rng.Offset(3, 2).Value
  22.          
  23.           Set rng = .Find(sMarkBuy, LookIn:=xlValues, lookat:=xlWhole)
  24.           If Not rng Is Nothing Then sBuy = rng.Offset(3, 2).Value
  25.          
  26.           d(sName) = Array(sSell, sBuy)
  27.         End With
  28. NEXT_BLOCK:
  29.       Next
  30.     Next
  31.   End With
  32.   
  33.   '¶K¤W»ù¦ì
  34.   With Sheets("¥D¤u§@ªí")
  35.     For Each x In .Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
  36.       If d.exists(x.Value) Then x.Offset(, 1).Resize(, 2).Value = d(x.Value)
  37.     Next
  38.   End With

  39. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# stillfish00


  ·P®¦ §Ú¥h¸Õ¸Õ!

TOP

¦^´_ 3# icestormer


    §A¦n µ{§Ç·|«Ü¶¶§Qªº¶]§¹¦ý¥L¤£·|§ó·s©O,SORRY§Ú¨S¦³»¡©ú¥Õ §Ú¬O¥Î¤U¤è«ü¥O³oºØ¨Ó§ó·s

  Selection.QueryTable.Refresh BackgroundQuery:=False

TOP

¦^´_ 4# icestormer
«ö¤W¤è[§ó·s»ù¦ì]¦r¼Ë(F1 G1Àx¦s®æ)

TOP

¥»©«³Ì«á¥Ñ icestormer ©ó 2014-1-15 18:21 ½s¿è

¦^´_ 5# stillfish00


   

¨º­Ó¦n¹³§A»~·|§Úªº·N«ä¤F

§Ú¦A¸ÕªÌ»¡©ú¤@¦¸ ¥D­n¬O¸ê®Æ·|¦³2 ¨ì3¤dµ§ ¦ý¬O¨C¦¸§Ú¤]³\¥u­n§ó·s¨ä¤¤1¨ì3¦Êµ§(¨CºØ¨C¦¸§ó·s®ÉºØÃþ¤£¦P)

§Ú«ö¤U §ó·s»ù¦ì®É ¥L¦n¹³¥u·|§ó·s ¥D¤u§@ªí¤WªºB2¨ìC4(¥Ø«e¤WÀY¥u¦³3µ§¸ê®Æ)¦Ó¤£·|¥h§ó·s ¸ê®Æ°Ï¤ºªº ¦p¤U¹Ï

§Ú·Q¤@­Óµ{¦¡¦b§Ú«ö¤U[§ó·s¥«»ù]®É ¥¦·|¦Û°Ê«ö[¥D¤u§@ªí]¤WA2¨ìAXXX(¬Ý¦³´Xµ§)
¨C¤@µ§¤Wªºª««~¦WºÙ(¨Ò¦p:Pyerite)¥h§ä¨ì¸ê®Æ°Ï¤º¶i¦Ó§ó·s¥¦ ,µM«á¥Ñ©ó¸ê®Æ¶q¤j©Ò¥H¥L¥i¯à·|A¨º©Î¬OH©Î¬O§ó»·ªºAC300¨º¸Ì
¦pªG¥þ³¡¸ê®Æ³£¦b¦P¤@¦æ(A1:A65536)¨º§ÚÁÙ¼gªº¥X¨Ó¦ý¸ê®Æ·|¤À´²¶}©Ò¥H¼g¤£¥X¨Ó><
¦A¦¸·PÁ§Aªº¦^ÂÐ!

TOP

¦^´_ 6# icestormer
©Ò¥H§A¸ê®Æªí¬O¦³«Ü¦h«Ü¦h¥~³¡¸ê®Æ³s½u²Õ¦¨¡A¥þ³¡³£§ó·s¤ÓºC©Ò¥H¬O­n§ä¨ì¯S©wªº¥~³¡¸ê®Æ§ó·s³s½u´N¦n¡A
µM«á¦A¨ú¦^¶R½æ»ù¨ì¥D¤u§@ªí¡A¹ï¶Ü¡H

­×§ï¥D¤u§@ªí
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.   If Application.Intersect(Target, Range("F1:G1")) Is Nothing Then Exit Sub
  3.   UpdateQueryTable
  4.   UpdatePrice
  5. End Sub
½Æ»s¥N½X
­ì¥»¤@¯ë¼Ò²Õ¦A¥[¤W:
  1. Sub UpdateQueryTable()
  2.   Dim ar, rng As Range, x
  3.   Dim sMsg As String, bNotFound As Boolean
  4.   
  5.   Const sMark = " - Market Browser"
  6.   sMsg = "¥H¤U§ä¤£¨ì : "
  7.   
  8.   With Sheets("¥D¤u§@ªí")
  9.     ar = Application.Transpose(.Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp)).Value)
  10.   End With
  11.   
  12.   With Sheets("¸ê®Æ°Ï")
  13.     For Each x In ar
  14.       Set rng = .Cells.Find(x & sMark, LookIn:=xlValues, lookat:=xlWhole)
  15.       If rng Is Nothing Then
  16.         sMsg = sMsg & vbCrLf & x & sMark
  17.         bNotFound = True
  18.       Else
  19.         rng.QueryTable.Refresh BackgroundQuery:=False
  20.       End If
  21.     Next
  22.   End With
  23.   
  24.   If bNotFound Then MsgBox sMsg Else MsgBox "Finish"
  25. End Sub
½Æ»s¥N½X

TOP

¦^´_ 7# stillfish00


   ¹ï ¨S¿ù ¤£µM§Ú¥u­n¬Ý300µ§¦ý¤@¦¸¥þ³¡§ó·s3¤dµ§...³o..¤Ó¨Sªá®É¶¡¤]¨S¥²­n  ·PÁ §Ú¸Õ¸Õ!^^

TOP

¥»©«³Ì«á¥Ñ icestormer ©ó 2014-1-16 09:53 ½s¿è

¦^´_ 8# icestormer


   

§A¦n §Ú¤@¶}©l ¸Õ³£OK(¦³3µ§) µM«á©O §Úµo²{·í¥u¦³¤@µ§¸ê®Æ®É(¦p¤W¹Ï)´N·|µo¥Í¿ù»~  ¥u­n¦³2µ§¥H¤W¸ê®Æ´N¤£·|¥X¿ù ¤£ª¾­n­×§ï¨º¸Ì©O?


¥t¥~ §Ú§â¨º¤@¦æ¥h±¼ updateprice   ­n¶]®É¦³¿ù»~

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2014-1-16 10:20 ½s¿è

¦^´_ 9# icestormer
§ï¤W­±
  With Sheets("¥D¤u§@ªí")
    ar = Application.Transpose(.Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp)).Value)
    If Not IsArray(ar) Then ar = Array(ar)
  End With


¥h±¼ updateprice   ­n¶]®É¦³¿ù»~
¤£À´¡A¤£¥i¯à¨ú®ø©I¥s¨ç¼Æ¤Ï¦Ó²£¥Í¿ù»~

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD