✔ 最佳答案
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$3" Then
If .Value <> "" Then
Application.DisplayAlerts = False
2013-05-08 21:34:01 補充:
TT = "=XQBTS|Quote!'" & .Value
[A1] = TT & ".TW-ID'"
[A2] = TT & ".TW-Name'"
End If
End If
End With
End Sub
DDE公式無法使用INDIRECT!
2013-05-09 17:31:47 補充:
如果工作表1 A1列~A20列的DDE公式文字都不一樣,
且除編號外,其它字元英數混合,就很難去抓取代位置!
有檔案看較準確!
這有兩題參考,看能否自行套用:
http://tw.knowledge.yahoo.com/question/question?qid=1013032004425
http://tw.knowledge.yahoo.com/question/question?qid=1613041903983
2013-05-10 14:22:22 補充:
〔代碼〕工作表區程式:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Or .Item(1) = "" Then Exit Sub
Sheets("DDE").Rows(.Row).Replace "!'*.", "!'" & .Text & ".", Lookat:=xlPart
End With
End Sub
2013-05-11 10:39:33 補充:
EXCEL VBA.輸入新編號,自動將DDE公式中的原編號全部取代
<.准提部林.>
---------------------------------
■公式範例:DDE公式工作表.第2列
B2:=XQBTS|Quote!'5398.TW-ID' C2:=XQBTS|Quote!'5398.TW-Name' D2:=XQBTS|Quote!'5398.TW-Time' E2:=XQBTS|Quote!'5398.TW-Price' F2:=XQBTS|Quote!'5398.TW-Volume' G2:=XQBTS|Quote!'5398.TW-PriceChange' H2:=XQBTS|Quote!'5398.TW-PriceChangeRatio' I2:=XQBTS|Quote!'5398.TW-Open' J2:=XQBTS|Quote!'5398.TW-High' K2:=XQBTS|Quote!'5398.TW-Low' L2:=XQBTS|Quote!'5398.TW-TotalVolume' M2:=XQBTS|Quote!'5398.TW-PreClose' N2:=XQBTS|Quote!'5398.TW-StockNetValue' O2:=XQBTS|Quote!'5398.TW-StockValueRatio' P2:=XQBTS|Quote!'5398.TW-PERatio' Q2:=XQBTS|Quote!'5398.TW-PBRatio'
--主要需求--
在〔代碼〕工作表A欄輸入新編號,自動取代〔同列〕公式的代碼。
例如:輸入〔2917〕,則將〔5398〕全數取代。
■程式碼1:限單格輸入.單格取代
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Or .Item(1) = "" Or .Row = 1 Then Exit Sub
Sheets("DDE").Rows(.Row).Replace "!'*.", "!'" & .Text & ".", Lookat:=xlPart
End With
End Sub
■程式碼2:可複製貼入單格或多格代碼,一次取代
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range
With Target
If .Columns.Count > 1 Or .Column > 1 Then Exit Sub
For Each xR In .Cells
If xR.Row > 1 And xR.Value <> "" Then
Sheets("DDE").Rows(xR.Row).Replace "!'*.", "!'" & xR.Text & ".", Lookat:=xlPart
End If
Next
End With
End Sub
---------------------------------