CODEWITHSUNDEEP
excelvba
Xander Roy
Xander Roy

Reputation: 29

Missing, Duplicate declaration in current scope

I'm currently writing a program to analyze some data, and my Debugger is throwing the "Duplicate Declaration in Current Scope" error, highlighting "Dim rTickers As Range". I cannot find a duplicate anywhere in here. Is there some other reason I could be getting this error? Thanks for your time. 

Sub TSV_Ticker()
    'Create Dictionary To get Unique Values From Column A
    Dim dTickers As New Dictionary
    Dim i As Long
    For i = 2 To Rows.Count
        On Error Resume Next
        dTickers.Add (Cells(i, 1).Value), CStr(Cells(i, 1).Value)
    Next

    'Create The Ticker And Sum Column Headers
    Range("J1").Value = "<Sum>"
    Range("I1").Value = "<Ticker>"

    'Define where we will be putting our keys
    Dim uTickers As Range
    Set uTickers = Range("I2")
    'Convert Keys into array for syntax reasons
    aTickers = dTickers.Keys
    'Resize the range so it will fit the array
    Set rTickers = rTickers.Resize(UBound(aTickers), 1)
    'Put array into range, verticaly
    rTickers.Value = Application.Transpose(aTickers)

    'Define Range of column A
    Dim rTickers As Range
    Set rTickers = Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Dim TSV As Long

    'Defining some Date Variables (Column B)
    Dim fDate As Integer
    Dim eDate As Integer
    Dim rDates As Range
    Set rDates = Range("B2:B" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)

    'And The Open / Close Variables (Colums C&F)
    Dim vOpen As Double
    Dim Vclose As Double
    Dim Delta As Double
    Dim pDelta As Double

    'Adding Some Columns
    sht.Range("J1").EntireColumn.Insert
    Range("J1").Value = "Yearly Change"
    sht.Range("K1").EntireColumn.Insert
    Range("K1").Value = "Percent Change"

    For Each Cell In rTickers
        'Searching our range that we put the Array in for matching Values
        Set t = rTickers.Find(Cell.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not t Is Empty Then
            'add column G's value to corresponding I value
            Cells(t.Row, 10).Value = Cells(t.Row, 10).Value + Cells(Cell.Row, 7).Value
        End If
    Next Cell
End Sub

Upvotes: 2

Views: 234

Answers (1)

L42
L42

Reputation: 19737

As commented, without using Option Explicit variables are created at the first instance.
So in your code, rTickers is already created when this below line is executed:

Set rTickers = rTickers.Resize(UBound(aTickers), 1)

That being said, below line will give you a compile error:

Dim rTickers As Range

enter image description here

because rTickers variable has already been created.
I'll post this as answer for others reference.
But if Rory or Ashlee wish to add their answers, I'll delete mine :).

Upvotes: 1

Related Questions