¡yµo°Ý¡z·s¼W¤u§@ªí¨Ã¦P®É¼g¤JCalculate¨Æ¥ó

ko310kmo

¥ý±qºô¸ô§ì¤U¼i¬ù»ù¡A¨Ã¨Ì©Ò»Ý±ø¥ó¿z¿ï«á¡A·s¼W¤u§@ªí¨Ã¨Ì³Ñ¤Uªº¼i¬ù»ù¶i¦æ­«·s©R¦W¨Ã¼g¤Jddeµ{¦¡(e-leader)¡A
¦Ó¨C­Ó·s¼Wªº¤u§@ªí­I«á³£»Ý­n¦³¦U¦ÛªºCalculate
¥»¨Ó¬O·Q¥Hµ{¦¡¼g¤Jµ{¦¡ªº¤è¦¡¡A¦ý§Ú¤£·|§ï¨Ì¥H¤U¤è¦¡¶i¦æ


½Æ»sB¬¡­¶Ã¯ªºSheet1¨ì¢Ï¬¡­¶Ã¯¡A¨Ã¨ÌA¬¡­¶Ã¯ªºSheet1ªº¼i¬ù»ù­«·s©R¦W¡A¦³´X­Ó´N½Æ»s´X­Ó¤u§@ªí¡C

¡¯¢Ï¬¡­¶Ã¯ªº¤@¯ë¼Ò²Õ



Sub ¤U¸ü§Y®É¸ê®Æ()
    Dim nRow As Integer
    Dim I   As Integer
    Dim X   As Integer
    Dim Y As Integer
    Dim J As Integer
    Dim QryTbl As QueryTable
    Dim WebAddress As String   
    WebAddress = "http://tw.futures.finance.yahoo.com/future/l/opt_TXO_1.html?rr=13022289583560.9952862945437091"
   
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells.Clear
        Set QryTbl = .QueryTables.Add("URL;" & WebAddress, .Range("A1"))
    End With
   
    With QryTbl
        .WebTables = "7,8,10"
        .WebFormatting = xlWebFormattingNone
        .WebSelectionType = xlSpecifiedTables
        .RefreshPeriod = 0
        .Refresh BackgroundQuery:=False
    End With
   
    With ThisWorkbook.Worksheets("Sheet1")
        .Columns("B:G").Clear  
        nRow = .Range("A65536").End(xlUp).Row - 6  
        .Range(.Cells(nRow + 6 - 15 + 1, 1), .Cells(nRow + 6, 1)).Delete (xlShiftUp)
        .Range("a7:a21").Delete (xlShiftUp)

        Y = .Range("A65536").End(xlUp).Row

    End With

   
    For I = 7 To Y
        J = J + 1
        X = Worksheets("Sheet1").Cells(I, 1)
        Workbooks("B").Worksheets("Sheet1").Copy _
        after:=Workbooks("A").Worksheets(1)
        ActiveSheet.Name = X
        ActiveSheet.Range("H1") = "¼i¬ù»ù"
        ActiveSheet.Range("H2") = X
        ActiveSheet.Range("I1") = "§Y®É¦¨¥æ»ù"
        ActiveSheet.Range("J1") = "¾ú¥v¦¨¥æ»ù"
            Select Case Len(ActiveSheet.Range("H2"))
                Case Is = 4
                    ActiveSheet.Range("I2").Formula = "=CATDDE|'FUTOPT<FO>TXO0" & X & "E1'!CurPrice"
                Case Is = 5
                    ActiveSheet.Range("I2").Formula = "=CATDDE|'FUTOPT<FO>TXO" & X & "E1'!CurPrice"
            End Select
    Next I
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*B¬¡­¶Ã¯ªºSheet1­I«áªºµ{¦¡½X¡A§Ú¼g¤J

Private Sub Worksheet_Calculate()
    Dim Z As Integer
    Static Q As Integer
    Dim P As Integer
    P = Workbooks("A").Worksheets("sheet1").Cells(6 + j, 1)
   
    With ThisWorkbook.Worksheets("" & P & "")

       If IsNumeric(.Range("I2").Value) = True Then
      
          Z = .Range("J65536").End(xlUp).Row
          .Cells(Z + 1, 10) = .Range("I2")
       End If
    End With
End Sub

---------------------------------------------------------------------
³o¼ËÁöµM¥i¥H¦P®É½Æ»s¤u§@ªí¨ì¢Ï¬¡­¶Ã¯¨Ã¨Ìsheet1ªº¼i¬ù»ù­«·s©R¦W¡A¦ý­I«áªºµ{¦¡½XCalculateªºp­È³£¬Û¦P¡A³£·|¬O³Ì«á¤@­Ó¼Æ­È¡A¦Ó¤£¬O±MÄÝ©ó¦Û¤w¤u§@ªíªº¨S¿ìªk¦U¦Û¿W¥ß¡]¤]´N¬O»¡8400ªº¤u§@ªíªºp¬O8700¡A8500ªº¤u§@ªí¤]¬O8700¡A¦ý§Ú·Q­nªº¬O8400ªºp¬O8400¡^¡A½Ð°Ý¸Ó¦p¦ó§ï¡H

ko310kmo

¦^´_ 4# GBKEE
¦pªG§ï¦¨Public J As Integer

P­È·|Åܦ¨Åã¥Ü³Ì«á¤@­Ó9600(³Ì«á¤@­Ó½Æ»sªº¤u§@ªíªºP­È¡Ap³Ì«á¶]¨ìªº¨º­ÓÄæ¦ì)
­«·s©R¦W«áªº¦U¤u§@ªíªºCalculate¤£·|¦U¦ÛÅã¥Ü¨ä©ÒÄݪº¢Þ­È.

GBKEE

¦^´_ 3# ko310kmo

¦bA¬¡­¶Ã¯ªº¤@¯ë¼Ò²Õ³Ì³»ºÝ,³]¥ß¤½¥ÎÅܼÆ.¸Õ¸Õ¬Ý

Public J As Integer
Sub ¤U¸ü§Y®É¸ê®Æ()
    Dim nRow As Integer
    Dim I   As Integer
    Dim X   As Integer
    Dim Y As Integer
   ' Dim J As Integer
    Dim QryTbl As QueryTable
    Dim WebAddress As String

ko310kmo

¦^´_ 2# Hsieh

P = Workbooks("A").Worksheets("sheet1").Cells(6 + j, 1)
«üªº¬OA¬¡­¶Ã¯ªº²Ä¤@Ä檺¼Æ¦C

§ï¦¨¤j¤j©Ò»¡ªºP =Cells(6 + j, 1)
·|¥X²{¡y°}¦C©Ò¤Þ­È¶W¥X½d³ò¡zªº¿ù»~¡AP·|Åã¥Ü¬°¢¯

·Ó§Úµo°Ýªº¼gªk¡Aµ{¦¡¥i¥H¶]ªº°Ê¡A¤u§@ªí·|¶K¹L¥h¨Ã­«·s©R¦W
¦ýP­È«o³£·|Åã¥Ü²Ä¤@­Ó8400
¦Ó¤£·|¦U¦ÛÅã¥Ü¨ä©ÒÄݪº¢Þ­È

§Ú§Æ±æªºµ²ªG¡Ð¡]¢Ð¬¡­¶Ã¯Sheet1¤w¨Æ¥ý¼g¦nCalculate¨Æ¥ó¡A½Æ»s¨ìA¬¡­¶Ã¯
¦A¨ÌA¬¡­¶Ã¯ªº²Ä¤@Ä檺¼Æ¦C¡A¨Ì§Ç­«·s©R¦W¡A
²Ä¤@­Ó½Æ»sªº¤u§@ªíªºP­È=8400
²Ä¤G­Ó½Æ»sªº¤u§@ªíªºP­È=8500
²Ä¤T­Ó½Æ»sªº¤u§@ªíªºP­È=8600¡^



§Ú·Q¬O§_¬O§Úªº¢Øªº«Å§i¦³¿ùor©ñªº¦ì¸m¦³¿ù
½Ð¤j¤j¶}¥Ü¡AÁÂÁ¡I¡I¡I

Hsieh

Worksheet_Calculate¬O¤u§@ªí¨Æ¥óµ{§Ç
©Ò¥H¤@©w¬O¦b¸Ó¤u§@ªí¼Ò²Õ
P =Cells(6 + j, 1)