Looping through 5 names and getting results in separate sheet in MS EXCEL

Question

Write now I am changing the stock symbol every time by clicking on my macro created button and entering the stock symbol in an input box. Suppose I have 10 stock symbols and I wanted to loop through each stock symbol and produce results in separate sheets. 10 different sheets for 10 different symbols. How am I supposed to overcome this problem. This process I am doing is very repetitive so any ideas? In real I have 1000 stock symbols.

Here's my code from which I am fetching my data and it looks like this right now.

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

Adding range of my sheet to dictionary.

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

Samuel Everson · Accepted Answer · 2021-05-11T06:24:48.570

You could check your StockSymbol range and create a Dictionary containing all StockSymbol values.

A Dictionary object stores a Key which allows you to check a large range of data and only capture each value once returning no duplicates.

You can then loop each key of your dictionary to be used to update you 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

To make it more dynamic, you can see how to find the last used row and use that with your TargetRange.

To have a new Worksheet used for each iteration, you'll need to use the Sheets.Add method to create a new sheet, then target that sheet for your output rather than ActiveSheet.

E.g.

Sub Foo()
    ActiveWorkbook.Sheets.Add 
End Sub

Note:

If Before and After are both omitted, the new sheet is inserted before the active sheet.

Thank you so much for a detailed answer I am learning the basics right now. I don't know how to add them to the dictionary. When I am done learning that I will follow your procedure. I'll get back to you then. — Daman deep, May 11 '21 at 06:40
See edited question I am trying to add elements in dictionary. When run it doesn't return anything. How do I check if elements are present in dictionary? — Daman deep, May 11 '21 at 07:11
It's better practice to ask your new question in a new question as it doesn't relate to the original question specifically - feel free to @ me and link the new question once you post it :) — Samuel Everson, May 11 '21 at 07:15

Looping through 5 names and getting results in separate sheet in MS EXCEL

1 Answers1