EXCEL VBA 如何將重複的程式合併再一起

ken2192

各位版友好，
以下是我在excel vba中，輸入股票代號就自動帶出相關DDE的程式，例如在A5輸入2498，b5~ae5就會跑出相關連結的DDE，但為了重複此程式應用在各列中，於是我只能複製貼上一樣的指令，並修改5-->6，帶出第6列的直，
但由於太繁瑣且要應用在5-25列，請問版友我如何將重複的指令合併在同一個程式裡，不管輸入在A5或A9他都可以自動帶出來!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" And [A5] <> "" Then
[b5] = "=XQCTS|Quote!'" & [A5] & ".TW-Name'"
[c5] = "=XQCTS|Quote!'" & [A5] & ".TW-Bid'"
[d5] = "=XQCTS|Quote!'" & [A5] & ".TW-Ask'"
[e5] = "=XQCTS|Quote!'" & [A5] & ".TW-Price'"
[f5] = "=XQCTS|Quote!'" & [A5] & ".TW-PriceChange'"
[g5] = "=XQCTS|Quote!'" & [A5] & ".TW-PriceChangeRatio'"
[i5] = "=XQCTS|Quote!'" & [A5] & ".TW-Volume'"
[n5] = "=XQCTS|Quote!'" & [A5] & ".TW-TotalVolume'"
[o5] = "=XQCTS|Quote!'" & [A5] & ".TW-PreTotalVolume'"
[r5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestBidSize1'"
[s5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestBid1'"
[t5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestAsk1'"
[u5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestAskSize1'"
[y5] = "=XQCTS|Quote!'" & [A5] & ".TW-PreClose'"
[z5] = "=XQCTS|Quote!'" & [A5] & ".TW-Open'"
[aa5] = "=XQCTS|Quote!'" & [A5] & ".TW-High'"
[ab5] = "=XQCTS|Quote!'" & [A5] & ".TW-Low'"
[ac5] = "=XQCTS|Quote!'" & [A5] & ".TW-UpLimit'"
[ad5] = "=XQCTS|Quote!'" & [A5] & ".TW-DownLimit'"
[ae5] = "=XQCTS|Quote!'" & [A5] & ".TW-Time'"
End If

If Target.Address = "$A$6" And [A6] <> "" Then
[b6] = "=XQCTS|Quote!'" & [A6] & ".TW-Name'"
[c6] = "=XQCTS|Quote!'" & [A6] & ".TW-Bid'"
[d6] = "=XQCTS|Quote!'" & [A6] & ".TW-Ask'"
[e6] = "=XQCTS|Quote!'" & [A6] & ".TW-Price'"
[f6] = "=XQCTS|Quote!'" & [A6] & ".TW-PriceChange'"
[g6] = "=XQCTS|Quote!'" & [A6] & ".TW-PriceChangeRatio'"
[i6] = "=XQCTS|Quote!'" & [A6] & ".TW-Volume'"
[n6] = "=XQCTS|Quote!'" & [A6] & ".TW-TotalVolume'"
[o6] = "=XQCTS|Quote!'" & [A6] & ".TW-PreTotalVolume'"
[r6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestBidSize1'"
[s6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestBid1'"
[t6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestAsk1'"
[u6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestAskSize1'"
[y6] = "=XQCTS|Quote!'" & [A6] & ".TW-PreClose'"
[z6] = "=XQCTS|Quote!'" & [A6] & ".TW-Open'"
[aa6] = "=XQCTS|Quote!'" & [A6] & ".TW-High'"
[ab6] = "=XQCTS|Quote!'" & [A6] & ".TW-Low'"
[ac6] = "=XQCTS|Quote!'" & [A6] & ".TW-UpLimit'"
[ad6] = "=XQCTS|Quote!'" & [A6] & ".TW-DownLimit'"
[ae6] = "=XQCTS|Quote!'" & [A6] & ".TW-Time'"
End If

If Target.Address = "$A$7" And [A7] <> "" Then
[b7] = "=XQCTS|Quote!'" & [A7] & ".TW-Name'"
[c7] = "=XQCTS|Quote!'" & [A7] & ".TW-Bid'"
[d7] = "=XQCTS|Quote!'" & [A7] & ".TW-Ask'"
[e7] = "=XQCTS|Quote!'" & [A7] & ".TW-Price'"
[f7] = "=XQCTS|Quote!'" & [A7] & ".TW-PriceChange'"
[g7] = "=XQCTS|Quote!'" & [A7] & ".TW-PriceChangeRatio'"
[i7] = "=XQCTS|Quote!'" & [A7] & ".TW-Volume'"
[n7] = "=XQCTS|Quote!'" & [A7] & ".TW-TotalVolume'"
[o7] = "=XQCTS|Quote!'" & [A7] & ".TW-PreTotalVolume'"
[r7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestBidSize1'"
[s7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestBid1'"
[t7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestAsk1'"
[u7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestAskSize1'"
[y7] = "=XQCTS|Quote!'" & [A7] & ".TW-PreClose'"
[z7] = "=XQCTS|Quote!'" & [A7] & ".TW-Open'"
[aa7] = "=XQCTS|Quote!'" & [A7] & ".TW-High'"
[ab7] = "=XQCTS|Quote!'" & [A7] & ".TW-Low'"
[ac7] = "=XQCTS|Quote!'" & [A7] & ".TW-UpLimit'"
[ad7] = "=XQCTS|Quote!'" & [A7] & ".TW-DownLimit'"
[ae7] = "=XQCTS|Quote!'" & [A7] & ".TW-Time'"
End If

End Sub

stillfish00

回復 1# ken2192

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim ddeString As String
   
3. 
   
4.     If Intersect(Target, Range("A5:A25")) Is Nothing Then Exit Sub
   
5.    
   
6.     If Target.Count = 1 And Len(Target.Cells(1).Value) > 0 Then
   
7.         Application.EnableEvents = False
   
8.         
   
9.         ddeString = "=XQCTS|Quote!'" & Target.Value
   
10.         With Target
    
11.             .Cells(1, 2).Value = ddeString & ".TW-Name'"
    
12.             .Cells(1, 3).Value = ddeString & ".TW-Bid'"
    
13.             .Cells(1, 4).Value = ddeString & ".TW-Ask'"
    
14.             .Cells(1, 5).Value = ddeString & ".TW-Price'"
    
15.             
    
16.             '略
    
17.             
    
18.         End With
    
19.         
    
20.         Application.EnableEvents = True
    
21.     End If
    
22. End Sub

複製代碼

ken2192

感恩stillfish00快速回復小弟我的問題，試起來沒甚麼問題!!