Can a Scripting Dictionary hold decimal values?

termeric

Board Regular
Joined
Jun 21, 2005
Messages
245
I'm working on a project and i want to create a scripting dictionary to store a decimal value for a list of locations. It looks like i am getting a 0 for all the decimals. do i need to declare the dictionary as a specific type to make this work?

Code:
Sub Realignment()
    ' Select Tools->References from the Visual Basic menu.
    ' Check box beside "Microsoft Scripting Runtime" in the list.
    ' this compares the first 2 columns on the case list to the first column on the stoploss report to then link to the MMACT Tab
    ' it also takes plan market from caselist and applies the adjustment to the comp from the control tab

Dim dict As New Scripting.Dictionary
Dim dictLocal As New Scripting.Dictionary
Dim dictPMkt As New Scripting.Dictionary
Dim ws As Worksheet, wsMM As Worksheet, wsSL As Worksheet
Dim r As Long, m As Long, s As Long, p As Long
Dim lr As Long, lrM As Long, lrS As Long, lrPM As Long
Dim Str As Long, Itm As Long
Dim StrS As Long, StrM As Long
Dim pMkt As String


Set ws = Sheets("Case List Input")
Set wsMM = Sheets("Comp")
Set wsSL = Sheets("SL Input")


lr = GetCountA("Case List Input", 1)
lrM = GetCountA("Comp", 1)
lrS = GetCountA("SL Input", 1)
lrPM = GetCountA("Control", 8)



wsMM.Range("AQ1").Value = "Target Stop Loss"
wsMM.Range("AR1").Value = "Sold Stop Loss"

'Build the Dictionarys for PSU/PHID & PSU/Local Market
For r = 2 To lr
    Str = ws.Range("A" & r).Value
    Itm = ws.Range("B" & r).Value
    pMkt = ws.Range("M" & r).Value
    
    'dictionary for PHID and PSU from case list
    If dict.Exists(Str) Then
    Else
        dict.Add key:=Str, Item:=Itm
    End If
    
    
    'dictionary for PSU and Planmkt from case list
    If dictLocal.Exists(Itm) Then
    Else
        dictLocal.Add key:=Itm, Item:=pMkt
    End If
       
Next r


'Build the Dictionary for Local Market / Adjustment Factor
For p = 2 To lrPM
    'dictionary for plan market adjustments from control
    pMkt = Sheets("Control").Range("H" & p).Value
    Itm = Sheets("Control").Range("L" & p).Value

    If dictPMkt.Exists(pMkt) Then
    Else
        dictPMkt.Add key:=pMkt, Item:=Itm
    End If
Next p




For s = 13 To lrS
    StrS = wsSL.Range("A" & s).Value
    If dict.Exists(StrS) Then
        StrM = dict(StrS)
        For m = 2 To lrM
            If StrM = wsMM.Range("A" & m).Value Then 'PSU
            '=((X13*(1+BQ13))/12)*(1+AU13)
            '=(X13*(1+BR13))/12
                If IsNumeric(wsSL.Range("AU" & s).Value) = True Then
                    pMkt = dictLocal(StrM)
                    wsMM.Range("AQ" & m).Value = ((wsSL.Range("X" & s).Value * (1 + wsSL.Range("BQ" & s).Value) / 12) * (1 + wsSL.Range("AU" & s).Value)) * (1 + dictPMkt(pMkt))
                End If
                wsMM.Range("AR" & m).Value = (wsSL.Range("X" & s).Value * (1 + wsSL.Range("BR" & s).Value) / 12)
                
                Debug.Print dictPMkt(pMkt) 'dictLocal(StrM)

                   
                
            End If
        Next m
    End If

Next s


Set dict = Nothing
Set dictLocal = Nothing
Set dictPMkt = Nothing

End Sub

This is a sample of the values i am trying to store in dictPMkt

AZ/MN
0.5​
Capitol
0.35​
Florida
0.876​
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Assuming the Variable Itm holds the decimal, you need to declare it as double, not Long.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,785
Messages
5,542,503
Members
410,559
Latest member
jordansmith6532
Top