时间戳

首页 > 解决方案 > 遍历 5 个名称并在 MS EXCEL 中的单独工作表中获取结果

excel - 遍历 5 个名称并在 MS EXCEL 中的单独工作表中获取结果

问题描述

现在写，我每次都通过单击我的宏创建按钮并在输入框中输入股票代码来更改股票代码。假设我有 10 个股票代码，我想遍历每个股票代码并在单独的表格中生成结果。10 个不同的工作表，用于 10 个不同的符号。我该如何克服这个问题。我正在做的这个过程非常重复，所以有什么想法吗？实际上，我有 1000 个股票代码。

在此处输入图像描述

这是我从中获取数据的代码，现在看起来像这样。

Option Explicit

Sub Changestockproperties()
'
' Changestockproperties Macro
'

'
    With ActiveWorkbook.Connections("NAME"). _
        OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "Select Quotedate, StockSymbol, COnvert(Float,ClosePrice) As ClosePrice, COnvert(Float,TRInverse) AS TRInverse, " & Chr(13) & "" & Chr(10) & "CO" _
        , _
        "nvert(Float,Osc_1VI) As Osc_1VI, Convert(Float,HeatmapTrans) as HeatmapTrans" & Chr(13) & "" & Chr(10) & "from FormulaHeatmapOscilator where St" _
        , "ockSymbol='" & InputBox("Enter Stock Symbol"), "' order by quotedate")
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=NAME;Password=NAME;Persist Security Info=True;Extended Properties=""DRIVER=SQL Server;SERVER=NAME" _
        , _
        "3;UID=sa;APP=Microsoft Office 2013;WSID=NAME;DATABASE=NAME"";Use Procedure for Prepare=1;Auto Transla" _
        , _
        "te=True;Packet Size=4096;Workstation ID=NAME;Use Encryption for Data=False;Tag with column collation when possible=Fa" _
        , "lse")
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("NAME")
        .Name = "NAME"
        .Description = ""
    End With
    ActiveWorkbook.Connections("NAME").Refresh
End Sub

将我的工作表范围添加到字典中。

Option Explicit
Public Sub ChangeStockSymbol()
    Dim d As Scripting.Dictionary
    Dim r As Excel.Range
    Dim c As Excel.Range
    
    Set d = New Scripting.Dictionary
    Set r = Range("A1:B1311")
    For Each c In r.Cells
    d.Add CStr(c.Address), c.Value
    Next c

End Sub

标签： excelvba

解决方案

您可以检查您的StockSymbol范围并创建一个包含所有 StockSymbol 值的字典。

一个Dictionary对象存储一个键，它允许您检查大量数据，并且只有在不返回重复项时才捕获每个值。

然后，您可以循环使用字典中的每个键来更新您的 StockSymbol。

Public Sub ChangeStockSymbol()
    Dim MyDictionary As Variant
    Dim TargetCell As Range
    Dim TargetRange As Range
    Dim DictionaryItem As Variant
    
    Set MyDictionary = CreateObject("Scripting.Dictionary")
    Set TargetRange = Sheet1.Range("A1:A10")    'Change this to target your correct range for the stocksymbols. 
    
    For Each TargetCell In TargetRange
        If Not MyDictionary.exists(TargetCell.Value) Then
            MyDictionary.Add TargetCell.Value, TargetCell.Value
        End If
    Next TargetCell
    
    For Each DictionaryItem In MyDictionary
        Debug.Print MyDictionary(DictionaryItem) 'Enter your code here to perform your update. 
    Next DictionaryItem
End Sub

为了使其更具动态性，您可以查看如何查找最后使用的行并将其与您的TargetRange.

要Worksheet为每次迭代使用新的，您需要使用Sheets.Add方法创建一个新工作表，然后将该工作表定位为您的输出而不是ActiveSheet.

例如

Sub Foo()
    ActiveWorkbook.Sheets.Add 
End Sub

笔记：

如果 Before 和 After 都被省略，则新工作表将插入到活动工作表之前。

推荐阅读